{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<style  type=\"text/css\"> \n",
    "cool {\n",
    "  width: 100px;\n",
    "  height: 100px;\n",
    "  background-color: red;\n",
    "  position: relative;\n",
    "  -webkit-animation-name: example; /* Safari 4.0 - 8.0 */\n",
    "  -webkit-animation-duration: 4s; /* Safari 4.0 - 8.0 */\n",
    "  -webkit-animation-iteration-count: infinite; /* Safari 4.0 - 8.0 */\n",
    "  animation-name: example;\n",
    "  animation-duration: 4s;\n",
    "  animation-iteration-count: infinite;\n",
    "}\n",
    "\n",
    "/* Safari 4.0 - 8.0 */\n",
    "@-webkit-keyframes example {\n",
    "  0%   {background-color:red; left:0px; top:0px;}\n",
    "  25%  {background-color:yellow; left:200px; top:0px;}\n",
    "  50%  {background-color:blue; left:200px; top:200px;}\n",
    "  75%  {background-color:green; left:0px; top:200px;}\n",
    "  100% {background-color:red; left:0px; top:0px;}\n",
    "}\n",
    "\n",
    "/* Standard syntax */\n",
    "@keyframes example {\n",
    "  0%   {background-color:red; left:0px; top:0px;}\n",
    "  25%  {background-color:yellow; left:200px; top:0px;}\n",
    "  50%  {background-color:blue; left:200px; top:200px;}\n",
    "  75%  {background-color:green; left:0px; top:200px;}\n",
    "  100% {background-color:red; left:0px; top:0px;}\n",
    "}\n",
    "</style>\n",
    "\n",
    "<b><center>\n",
    "<span style=\"font-size: 24pt; line-height: 1.2\">\n",
    "COMS W4111: Introduction to Databases<br>\n",
    "Spring 2020, Sections 002, H02, V02\n",
    "</span>\n",
    "</center></b>\n",
    "</span><br>\n",
    "<p>\n",
    "<i><center>\n",
    "<span style=\"font-size: 20pt; line-height: 1.2\">\n",
    "Programming Project Notebook<br>\n",
    "</span>\n",
    "</center></i>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Introduction\n",
    "\n",
    "- This notebook defines HW1, Part C for the programming track.\n",
    "\n",
    "\n",
    "- Submission instructions:\n",
    "    - Submit a zip file containing all of the files in the HW project directory that you clone (see below).\n",
    "    - Much of your work is completing sections of this notebook, but you MUST edit the copy in the project directory.\n",
    "    - If you reference or create any files, please create in the project directory.\n",
    "    - The name of your zip file MUST be ```uni_W4111_s20_hw1c,```  replace ```uni``` with you UNI. My submission would be ```dff9_W4111_s20_hw1c.```\n",
    "    - The homework is due on 23-February-2020 at 11:59 PM. You MAY use late days.\n",
    "    \n",
    "- We will use the pinned [Piazza post](https://piazza.com/class/k5grpo7gmn71pj?cid=301) to clarify the homework and answer questions.\n",
    "\n",
    "\n",
    "# Common Setup\n",
    "\n",
    "Both tracks should:\n",
    "- MySQL: Install\n",
    "    - [MySQL Server Community Edition](https://dev.mysql.com/doc/mysql-installation-excerpt/8.0/en/)\n",
    "    - [MySQL Workbench](https://dev.mysql.com/doc/workbench/en/)\n",
    "    - In addition to the official links, there are many operating specific tutorials.\n",
    "    \n",
    "    \n",
    "- Create a [Lucidchart educational account.](https://lucidchart.zendesk.com/hc/en-us/articles/207324096-Get-Started-with-an-Educational-Account)\n",
    "\n",
    "\n",
    "- Project template:\n",
    "    - If you do not have ```Git``` on your computer, please install it.\n",
    "    - Homework 1 setup instructions recommended that you create a folder ```/IntroToDB``` on your desktop.\n",
    "    - Under this directory, create a folder ```HW1C.```\n",
    "    - Open terminal shell or Git enabled command prompt.\n",
    "    - CD to the directory you created and enter ```git clone https://github.com/donald-f-ferguson/GoTHW```\n",
    "    - This will create the project template for you.\n",
    "        \n",
    "| <img src=\"./got_hw_project.png\"> |\n",
    "| :---: |\n",
    "| __HW1C Project Template__ |\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Run the following cells. Your answers WILL BE different than the sample output."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "======================================== Path Information ========================================\n",
      "Path =  /Users/donaldferguson/OneDrive - ANSYS, Inc/Columbia/Solution_GoTHW\n",
      "======================================== System Information ========================================\n",
      "System: Darwin\n",
      "Node Name: MacBook-Pro.local\n",
      "Release: 19.2.0\n",
      "Version: Darwin Kernel Version 19.2.0: Sat Nov  9 03:47:04 PST 2019; root:xnu-6153.61.1~20/RELEASE_X86_64\n",
      "Machine: x86_64\n",
      "Processor: i386\n",
      "======================================== Network Information ========================================\n",
      "Your Computer Name is:MacBook-Pro.local\n",
      "Your Computer IP Address is:127.0.0.1\n",
      "Your MAC address is: 0xacde48001122\n"
     ]
    }
   ],
   "source": [
    "import sys\n",
    "import os\n",
    "import platform\n",
    "import psutil\n",
    "\n",
    "print(\"=\"*40, \"Path Information\", \"=\"*40)\n",
    "print(\"Path = \", os.getcwd())\n",
    "\n",
    "print(\"=\"*40, \"System Information\", \"=\"*40)\n",
    "uname = platform.uname()\n",
    "print(f\"System: {uname.system}\")\n",
    "print(f\"Node Name: {uname.node}\")\n",
    "print(f\"Release: {uname.release}\")\n",
    "print(f\"Version: {uname.version}\")\n",
    "print(f\"Machine: {uname.machine}\")\n",
    "print(f\"Processor: {uname.processor}\")\n",
    "\n",
    "print(\"=\"*40, \"Network Information\", \"=\"*40)\n",
    "\n",
    "import socket    \n",
    "hostname = socket.gethostname()    \n",
    "IPAddr = socket.gethostbyname(hostname)    \n",
    "print(\"Your Computer Name is:\" + hostname)    \n",
    "print(\"Your Computer IP Address is:\" + IPAddr) \n",
    "\n",
    "import uuid\n",
    "print (\"Your MAC address is:\", hex(uuid.getnode())) \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Programming\n",
    "\n",
    "## Overview\n",
    "\n",
    "- Ensuring that programming track students build core skills is essential. Programming track assignments will contain a subset of the broader, more complete tasks assigned in the No programming assignments. So, there is a common core section to the programming track homework.\n",
    "\n",
    "\n",
    "- Additionally, the programming track will have tasks that reinforce skills necessary for programming and databases.\n",
    "\n",
    "\n",
    "## Tasks\n",
    "\n",
    "- Data Import: Import the tables in the Data/csv folder\n",
    "\n",
    "\n",
    "- Data Cleanup: Clean up the imported tables\n",
    "\n",
    "\n",
    "- Schema Improvement: For the following tables, perform the schema improvement tasks\n",
    "    1. ```characters```\n",
    "    2. ```scenes```\n",
    "    3. ```scenes_characters```\n",
    "\n",
    "\n",
    "- Views: Create the following views\n",
    "    1. Implement the view \"Character Interactions.\"\n",
    "    2. Define, explain and implement a view of you choosing.\n",
    "    \n",
    "    \n",
    "- Programming Section: Complete the implementation for the BaseDataTable and RDBDataTable classes\n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Task 1: Initial Data Import\n",
    "\n",
    "### Task\n",
    "\n",
    "- The folder in the project ```Data/csv``` contains several CSV files:\n",
    "    - ```'character_relationships.csv```\n",
    "    - ```characters.csv```\n",
    "    - ```characters_actors.csv```\n",
    "    - ```episodes.csv```\n",
    "    - ```locations.csv```\n",
    "    - ```scenes.csv```\n",
    "    - ```scenes_characters.csv```\n",
    "    \n",
    "    \n",
    "- Use MySQL Workbench:\n",
    "    - Create a new schema ```W4111GoTSolution``` and set as the default schema.\n",
    "    - Use the MySQL [Workbench Table Data Import Wizard](https://dev.mysql.com/doc/workbench/en/wb-admin-export-import-table.html) and import each of the files. Choose the option to create a new table."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Tests"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The sql extension is already loaded. To reload it, use:\n",
      "  %reload_ext sql\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "'Connected: root@W4111GoTSolution'"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%load_ext sql\n",
    "%sql mysql+pymysql://root:ttl980305@localhost/W4111GoTSolution"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```1.``` Find all the characters with last name ```Stark.```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Environment variable $DATABASE_URL not set, and no connect string given.\n",
      "Connection info needed in SQLAlchemy format, example:\n",
      "               postgresql://username:password@hostname/dbname\n",
      "               or an existing connection: dict_keys([])\n"
     ]
    }
   ],
   "source": [
    "%sql select * from characters where characterName like(\"% Stark\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```2.``` Find all of the information about scenes in season 1, episode 1."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@localhost/W4111GoTSolution\n",
      "36 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>id</th>\n",
       "        <th>seasonNum</th>\n",
       "        <th>episodeNum</th>\n",
       "        <th>sceneNo</th>\n",
       "        <th>sceneStart</th>\n",
       "        <th>sceneEnd</th>\n",
       "        <th>location</th>\n",
       "        <th>subLocation</th>\n",
       "        <th>greensight</th>\n",
       "        <th>altLocation</th>\n",
       "        <th>warg</th>\n",
       "        <th>flashback</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>0</td>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>0:00:40</td>\n",
       "        <td>0:01:45</td>\n",
       "        <td>The Wall</td>\n",
       "        <td>Castle Black</td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>2</td>\n",
       "        <td>0:01:45</td>\n",
       "        <td>0:03:24</td>\n",
       "        <td>North of the Wall</td>\n",
       "        <td>The Haunted Forest</td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>3</td>\n",
       "        <td>0:03:24</td>\n",
       "        <td>0:03:31</td>\n",
       "        <td>North of the Wall</td>\n",
       "        <td>The Haunted Forest</td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>4</td>\n",
       "        <td>0:03:31</td>\n",
       "        <td>0:03:38</td>\n",
       "        <td>North of the Wall</td>\n",
       "        <td>The Haunted Forest</td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>5</td>\n",
       "        <td>0:03:38</td>\n",
       "        <td>0:03:44</td>\n",
       "        <td>North of the Wall</td>\n",
       "        <td>The Haunted Forest</td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>6</td>\n",
       "        <td>0:03:44</td>\n",
       "        <td>0:05:36</td>\n",
       "        <td>North of the Wall</td>\n",
       "        <td>The Haunted Forest</td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>6</td>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>7</td>\n",
       "        <td>0:05:36</td>\n",
       "        <td>0:05:41</td>\n",
       "        <td>North of the Wall</td>\n",
       "        <td>The Haunted Forest</td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>7</td>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>8</td>\n",
       "        <td>0:05:41</td>\n",
       "        <td>0:05:48</td>\n",
       "        <td>North of the Wall</td>\n",
       "        <td>The Haunted Forest</td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>8</td>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>9</td>\n",
       "        <td>0:05:48</td>\n",
       "        <td>0:05:58</td>\n",
       "        <td>North of the Wall</td>\n",
       "        <td>The Haunted Forest</td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>9</td>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>10</td>\n",
       "        <td>0:05:58</td>\n",
       "        <td>0:06:21</td>\n",
       "        <td>North of the Wall</td>\n",
       "        <td>The Haunted Forest</td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10</td>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>11</td>\n",
       "        <td>0:06:21</td>\n",
       "        <td>0:06:39</td>\n",
       "        <td>North of the Wall</td>\n",
       "        <td>The Haunted Forest</td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>11</td>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>12</td>\n",
       "        <td>0:06:39</td>\n",
       "        <td>0:06:49</td>\n",
       "        <td>North of the Wall</td>\n",
       "        <td>The Haunted Forest</td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>12</td>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>13</td>\n",
       "        <td>0:06:49</td>\n",
       "        <td>0:07:45</td>\n",
       "        <td>North of the Wall</td>\n",
       "        <td>The Haunted Forest</td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>13</td>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>14</td>\n",
       "        <td>0:09:27</td>\n",
       "        <td>0:12:38</td>\n",
       "        <td>The North</td>\n",
       "        <td>Winterfell</td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>14</td>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>15</td>\n",
       "        <td>0:12:38</td>\n",
       "        <td>0:15:41</td>\n",
       "        <td>The North</td>\n",
       "        <td>Outside Winterfell</td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>15</td>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>16</td>\n",
       "        <td>0:15:41</td>\n",
       "        <td>0:18:44</td>\n",
       "        <td>The North</td>\n",
       "        <td>Outside Winterfell</td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>16</td>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>17</td>\n",
       "        <td>0:18:44</td>\n",
       "        <td>0:20:45</td>\n",
       "        <td>The Crownlands</td>\n",
       "        <td>King&#x27;s Landing</td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>17</td>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>18</td>\n",
       "        <td>0:20:45</td>\n",
       "        <td>0:22:43</td>\n",
       "        <td>The North</td>\n",
       "        <td>Winterfell</td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>18</td>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>19</td>\n",
       "        <td>0:22:43</td>\n",
       "        <td>0:23:09</td>\n",
       "        <td>The North</td>\n",
       "        <td>Winterfell</td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>19</td>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>20</td>\n",
       "        <td>0:23:09</td>\n",
       "        <td>0:23:39</td>\n",
       "        <td>The North</td>\n",
       "        <td>Winterfell</td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>20</td>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>21</td>\n",
       "        <td>0:23:39</td>\n",
       "        <td>0:29:16</td>\n",
       "        <td>The North</td>\n",
       "        <td>Winterfell</td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>21</td>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>22</td>\n",
       "        <td>0:29:16</td>\n",
       "        <td>0:30:47</td>\n",
       "        <td>The North</td>\n",
       "        <td>Winterfell</td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>22</td>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>23</td>\n",
       "        <td>0:30:47</td>\n",
       "        <td>0:32:57</td>\n",
       "        <td>The North</td>\n",
       "        <td>Winterfell</td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>23</td>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>24</td>\n",
       "        <td>0:32:57</td>\n",
       "        <td>0:33:45</td>\n",
       "        <td>The North</td>\n",
       "        <td>Winterfell</td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>24</td>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>25</td>\n",
       "        <td>0:33:45</td>\n",
       "        <td>0:36:17</td>\n",
       "        <td>Pentos</td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>25</td>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>26</td>\n",
       "        <td>0:36:17</td>\n",
       "        <td>0:38:23</td>\n",
       "        <td>Pentos</td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>26</td>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>27</td>\n",
       "        <td>0:38:23</td>\n",
       "        <td>0:40:05</td>\n",
       "        <td>Pentos</td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>27</td>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>28</td>\n",
       "        <td>0:40:05</td>\n",
       "        <td>0:40:56</td>\n",
       "        <td>The North</td>\n",
       "        <td>Winterfell</td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>28</td>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>29</td>\n",
       "        <td>0:40:56</td>\n",
       "        <td>0:41:19</td>\n",
       "        <td>The North</td>\n",
       "        <td>Winterfell</td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>29</td>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>30</td>\n",
       "        <td>0:41:19</td>\n",
       "        <td>0:44:14</td>\n",
       "        <td>The North</td>\n",
       "        <td>Winterfell</td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>30</td>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>31</td>\n",
       "        <td>0:44:14</td>\n",
       "        <td>0:47:28</td>\n",
       "        <td>The North</td>\n",
       "        <td>Winterfell</td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>31</td>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>32</td>\n",
       "        <td>0:47:28</td>\n",
       "        <td>0:50:29</td>\n",
       "        <td>The North</td>\n",
       "        <td>Winterfell</td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>32</td>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>33</td>\n",
       "        <td>0:50:29</td>\n",
       "        <td>0:56:05</td>\n",
       "        <td>Pentos</td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>33</td>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>34</td>\n",
       "        <td>0:56:05</td>\n",
       "        <td>0:57:48</td>\n",
       "        <td>Pentos</td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>34</td>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>35</td>\n",
       "        <td>0:57:48</td>\n",
       "        <td>0:59:06</td>\n",
       "        <td>The North</td>\n",
       "        <td>Winterfell</td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>35</td>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>36</td>\n",
       "        <td>0:59:06</td>\n",
       "        <td>1:00:57</td>\n",
       "        <td>The North</td>\n",
       "        <td>Winterfell</td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(0, 1, 1, 1, '0:00:40', '0:01:45', 'The Wall', 'Castle Black', '', '', '', ''),\n",
       " (1, 1, 1, 2, '0:01:45', '0:03:24', 'North of the Wall', 'The Haunted Forest', '', '', '', ''),\n",
       " (2, 1, 1, 3, '0:03:24', '0:03:31', 'North of the Wall', 'The Haunted Forest', '', '', '', ''),\n",
       " (3, 1, 1, 4, '0:03:31', '0:03:38', 'North of the Wall', 'The Haunted Forest', '', '', '', ''),\n",
       " (4, 1, 1, 5, '0:03:38', '0:03:44', 'North of the Wall', 'The Haunted Forest', '', '', '', ''),\n",
       " (5, 1, 1, 6, '0:03:44', '0:05:36', 'North of the Wall', 'The Haunted Forest', '', '', '', ''),\n",
       " (6, 1, 1, 7, '0:05:36', '0:05:41', 'North of the Wall', 'The Haunted Forest', '', '', '', ''),\n",
       " (7, 1, 1, 8, '0:05:41', '0:05:48', 'North of the Wall', 'The Haunted Forest', '', '', '', ''),\n",
       " (8, 1, 1, 9, '0:05:48', '0:05:58', 'North of the Wall', 'The Haunted Forest', '', '', '', ''),\n",
       " (9, 1, 1, 10, '0:05:58', '0:06:21', 'North of the Wall', 'The Haunted Forest', '', '', '', ''),\n",
       " (10, 1, 1, 11, '0:06:21', '0:06:39', 'North of the Wall', 'The Haunted Forest', '', '', '', ''),\n",
       " (11, 1, 1, 12, '0:06:39', '0:06:49', 'North of the Wall', 'The Haunted Forest', '', '', '', ''),\n",
       " (12, 1, 1, 13, '0:06:49', '0:07:45', 'North of the Wall', 'The Haunted Forest', '', '', '', ''),\n",
       " (13, 1, 1, 14, '0:09:27', '0:12:38', 'The North', 'Winterfell', '', '', '', ''),\n",
       " (14, 1, 1, 15, '0:12:38', '0:15:41', 'The North', 'Outside Winterfell', '', '', '', ''),\n",
       " (15, 1, 1, 16, '0:15:41', '0:18:44', 'The North', 'Outside Winterfell', '', '', '', ''),\n",
       " (16, 1, 1, 17, '0:18:44', '0:20:45', 'The Crownlands', \"King's Landing\", '', '', '', ''),\n",
       " (17, 1, 1, 18, '0:20:45', '0:22:43', 'The North', 'Winterfell', '', '', '', ''),\n",
       " (18, 1, 1, 19, '0:22:43', '0:23:09', 'The North', 'Winterfell', '', '', '', ''),\n",
       " (19, 1, 1, 20, '0:23:09', '0:23:39', 'The North', 'Winterfell', '', '', '', ''),\n",
       " (20, 1, 1, 21, '0:23:39', '0:29:16', 'The North', 'Winterfell', '', '', '', ''),\n",
       " (21, 1, 1, 22, '0:29:16', '0:30:47', 'The North', 'Winterfell', '', '', '', ''),\n",
       " (22, 1, 1, 23, '0:30:47', '0:32:57', 'The North', 'Winterfell', '', '', '', ''),\n",
       " (23, 1, 1, 24, '0:32:57', '0:33:45', 'The North', 'Winterfell', '', '', '', ''),\n",
       " (24, 1, 1, 25, '0:33:45', '0:36:17', 'Pentos', '', '', '', '', ''),\n",
       " (25, 1, 1, 26, '0:36:17', '0:38:23', 'Pentos', '', '', '', '', ''),\n",
       " (26, 1, 1, 27, '0:38:23', '0:40:05', 'Pentos', '', '', '', '', ''),\n",
       " (27, 1, 1, 28, '0:40:05', '0:40:56', 'The North', 'Winterfell', '', '', '', ''),\n",
       " (28, 1, 1, 29, '0:40:56', '0:41:19', 'The North', 'Winterfell', '', '', '', ''),\n",
       " (29, 1, 1, 30, '0:41:19', '0:44:14', 'The North', 'Winterfell', '', '', '', ''),\n",
       " (30, 1, 1, 31, '0:44:14', '0:47:28', 'The North', 'Winterfell', '', '', '', ''),\n",
       " (31, 1, 1, 32, '0:47:28', '0:50:29', 'The North', 'Winterfell', '', '', '', ''),\n",
       " (32, 1, 1, 33, '0:50:29', '0:56:05', 'Pentos', '', '', '', '', ''),\n",
       " (33, 1, 1, 34, '0:56:05', '0:57:48', 'Pentos', '', '', '', '', ''),\n",
       " (34, 1, 1, 35, '0:57:48', '0:59:06', 'The North', 'Winterfell', '', '', '', ''),\n",
       " (35, 1, 1, 36, '0:59:06', '1:00:57', 'The North', 'Winterfell', '', '', '', '')]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select * from scenes where episodeNum=1 and seasonNum=1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```3.``` Find all of the characters and actors who played them."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@localhost/W4111GoTSolution\n",
      "408 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>character_id</th>\n",
       "        <th>characterName</th>\n",
       "        <th>actorName</th>\n",
       "        <th>season</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_0</td>\n",
       "        <td>Addam Marbrand</td>\n",
       "        <td>B.J. Hogg</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_2</td>\n",
       "        <td>Aeron Greyjoy</td>\n",
       "        <td>Michael Feast</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_3</td>\n",
       "        <td>Aerys II Targaryen</td>\n",
       "        <td>David Rintoul</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_4</td>\n",
       "        <td>Akho</td>\n",
       "        <td>Chuku Modu</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_5</td>\n",
       "        <td>Alliser Thorne</td>\n",
       "        <td>Owen Teale</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_6</td>\n",
       "        <td>Alton Lannister</td>\n",
       "        <td>Karl Davies</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_7</td>\n",
       "        <td>Alys Karstark</td>\n",
       "        <td>Megan Parkinson</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_8</td>\n",
       "        <td>Amory Lorch</td>\n",
       "        <td>Fintan McKeown</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_9</td>\n",
       "        <td>Anguy</td>\n",
       "        <td>Philip McGinley</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_10</td>\n",
       "        <td>Archmaester Marwyn</td>\n",
       "        <td>Jim Broadbent</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_11</td>\n",
       "        <td>Areo Hotah</td>\n",
       "        <td>Deobia Oparei</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_12</td>\n",
       "        <td>Armeca</td>\n",
       "        <td>Sahara Knite</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_13</td>\n",
       "        <td>Arthur</td>\n",
       "        <td>Nathanael Saleh</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_14</td>\n",
       "        <td>Arthur Dayne</td>\n",
       "        <td>Luke Roberts</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_15</td>\n",
       "        <td>Arya Stark</td>\n",
       "        <td>Maisie Williams</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_16</td>\n",
       "        <td>Baby Sam</td>\n",
       "        <td>William Wilson</td>\n",
       "        <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_16</td>\n",
       "        <td>Baby Sam</td>\n",
       "        <td>James Wilson</td>\n",
       "        <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_17</td>\n",
       "        <td>Balon Greyjoy</td>\n",
       "        <td>Patrick Malahide</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_18</td>\n",
       "        <td>Baratheon Guard</td>\n",
       "        <td>Phil Barnhill</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_19</td>\n",
       "        <td>Barristan Selmy</td>\n",
       "        <td>Ian McElhinney</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_20</td>\n",
       "        <td>Benjen Stark</td>\n",
       "        <td>Joseph Mawle</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_21</td>\n",
       "        <td>Beric Dondarrion</td>\n",
       "        <td>Richard Dormer</td>\n",
       "        <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_21</td>\n",
       "        <td>Beric Dondarrion</td>\n",
       "        <td>Richard Dormer</td>\n",
       "        <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_21</td>\n",
       "        <td>Beric Dondarrion</td>\n",
       "        <td>Richard Dormer</td>\n",
       "        <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_21</td>\n",
       "        <td>Beric Dondarrion</td>\n",
       "        <td>Richard Dormer</td>\n",
       "        <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_21</td>\n",
       "        <td>Beric Dondarrion</td>\n",
       "        <td>David Michael Scott</td>\n",
       "        <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_22</td>\n",
       "        <td>Bianca</td>\n",
       "        <td>Eline Powell</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_23</td>\n",
       "        <td>Biter</td>\n",
       "        <td>Gerard Jordan</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_24</td>\n",
       "        <td>Black Lorren</td>\n",
       "        <td>Forbes KB</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_25</td>\n",
       "        <td>Black Walder Rivers</td>\n",
       "        <td>Tim Plester</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_26</td>\n",
       "        <td>Bobono</td>\n",
       "        <td>Leigh Gill</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_27</td>\n",
       "        <td>Bowen Marsh</td>\n",
       "        <td>Michael Condron</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_29</td>\n",
       "        <td>Bran Stark</td>\n",
       "        <td>Isaac Hempstead Wright</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_30</td>\n",
       "        <td>Brea</td>\n",
       "        <td>Gemita Samarra</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_31</td>\n",
       "        <td>Brienne of Tarth</td>\n",
       "        <td>Gwendoline Christie</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_32</td>\n",
       "        <td>Bronn</td>\n",
       "        <td>Jerome Flynn</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_33</td>\n",
       "        <td>Brother Ray</td>\n",
       "        <td>Ian McShane</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_34</td>\n",
       "        <td>Brynden Tully</td>\n",
       "        <td>Clive Russell</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_35</td>\n",
       "        <td>Captain of the Archers</td>\n",
       "        <td>Michael Grennell</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_36</td>\n",
       "        <td>Catelyn Stark</td>\n",
       "        <td>Michelle Fairley</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_37</td>\n",
       "        <td>Catspaw Assassin</td>\n",
       "        <td>Lalor Roddy</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_38</td>\n",
       "        <td>Cersei Lannister</td>\n",
       "        <td>Lena Headey</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_39</td>\n",
       "        <td>Child of the Forest</td>\n",
       "        <td>Alice Hewkin</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_40</td>\n",
       "        <td>Citadel Maester</td>\n",
       "        <td>Frank Hvam</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_41</td>\n",
       "        <td>Colen of Greenpools</td>\n",
       "        <td>Darren Killeen</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_42</td>\n",
       "        <td>Craster</td>\n",
       "        <td>Robert Pugh</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_43</td>\n",
       "        <td>Daario Naharis</td>\n",
       "        <td>Ed Skrein</td>\n",
       "        <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_43</td>\n",
       "        <td>Daario Naharis</td>\n",
       "        <td>Michiel Huisman</td>\n",
       "        <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_43</td>\n",
       "        <td>Daario Naharis</td>\n",
       "        <td>Michiel Huisman</td>\n",
       "        <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_43</td>\n",
       "        <td>Daario Naharis</td>\n",
       "        <td>Michiel Huisman</td>\n",
       "        <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_44</td>\n",
       "        <td>Daenerys Targaryen</td>\n",
       "        <td>Emilia Clarke</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_45</td>\n",
       "        <td>Dagmer Cleftjaw</td>\n",
       "        <td>Ralph Ineson</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_46</td>\n",
       "        <td>Daisy</td>\n",
       "        <td>Maisie Dee</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_47</td>\n",
       "        <td>Davos Seaworth</td>\n",
       "        <td>Liam Cunningham</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_48</td>\n",
       "        <td>Dickon Tarly</td>\n",
       "        <td>Freddie Stroma</td>\n",
       "        <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_48</td>\n",
       "        <td>Dickon Tarly</td>\n",
       "        <td>Tom Hopper</td>\n",
       "        <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_49</td>\n",
       "        <td>Dongo the Giant</td>\n",
       "        <td>Ian Whyte</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_50</td>\n",
       "        <td>Donnel Waynwood</td>\n",
       "        <td>Alisdair Simpson</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_51</td>\n",
       "        <td>Dontos Hollard</td>\n",
       "        <td>Tony Way</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_52</td>\n",
       "        <td>Doran Martell</td>\n",
       "        <td>Alexander Siddig</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_53</td>\n",
       "        <td>Doreah</td>\n",
       "        <td>Roxanne McKee</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_54</td>\n",
       "        <td>Dothraki Bloodrider #1</td>\n",
       "        <td>Diogo Sales</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_55</td>\n",
       "        <td>Dothraki Bloodrider #2</td>\n",
       "        <td>Junade Khan</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_56</td>\n",
       "        <td>Drennan</td>\n",
       "        <td>David Coakley</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_58</td>\n",
       "        <td>Drowned Priest</td>\n",
       "        <td>Jonathan Ryan</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_59</td>\n",
       "        <td>Eddard Stark</td>\n",
       "        <td>Sean Bean</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_60</td>\n",
       "        <td>Eddison Tollett</td>\n",
       "        <td>Ben Crompton</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_61</td>\n",
       "        <td>Edmure Tully</td>\n",
       "        <td>Tobias Menzies</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_62</td>\n",
       "        <td>Ellaria Sand</td>\n",
       "        <td>Indira Varma</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_64</td>\n",
       "        <td>Eon Hunter</td>\n",
       "        <td>Barrington Cullen</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_65</td>\n",
       "        <td>Euron Greyjoy</td>\n",
       "        <td>Pilou Asbæk</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_66</td>\n",
       "        <td>Faceless Man</td>\n",
       "        <td>Cedric Henderson</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_67</td>\n",
       "        <td>Farlen</td>\n",
       "        <td>Peter Ballance</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_68</td>\n",
       "        <td>Gared</td>\n",
       "        <td>Dermot Keaney</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_69</td>\n",
       "        <td>Gatins</td>\n",
       "        <td>Ricky Champ</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_70</td>\n",
       "        <td>Gendry</td>\n",
       "        <td>Joe Dempsie</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_71</td>\n",
       "        <td>Gerold Hightower</td>\n",
       "        <td>Eddie Eyre</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_73</td>\n",
       "        <td>Gilly</td>\n",
       "        <td>Hannah Murray</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_74</td>\n",
       "        <td>Goldcloak</td>\n",
       "        <td>Alan Paris</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_75</td>\n",
       "        <td>Goldcloak</td>\n",
       "        <td>Sam Callis</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_76</td>\n",
       "        <td>Goldcloak #1</td>\n",
       "        <td>Nikovich Sammut</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_77</td>\n",
       "        <td>Goldcloak #2</td>\n",
       "        <td>Seamus Kelly</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_78</td>\n",
       "        <td>Grand Maester Pycelle</td>\n",
       "        <td>Julian Glover</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_79</td>\n",
       "        <td>Great Master #1</td>\n",
       "        <td>Emilio Doorgasingh</td>\n",
       "        <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_79</td>\n",
       "        <td>Great Master #1</td>\n",
       "        <td>Gianpiero Cognoli</td>\n",
       "        <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_80</td>\n",
       "        <td>Great Master #2</td>\n",
       "        <td>Derek Horsham</td>\n",
       "        <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_80</td>\n",
       "        <td>Great Master #2</td>\n",
       "        <td>Bobby Asghar</td>\n",
       "        <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_81</td>\n",
       "        <td>Great Master #3</td>\n",
       "        <td>Peter Silverleaf</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_82</td>\n",
       "        <td>Great Master #4</td>\n",
       "        <td>Davor Jozinovic</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_83</td>\n",
       "        <td>Great Master #5</td>\n",
       "        <td>Ivan Peric</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_84</td>\n",
       "        <td>Great Master #6</td>\n",
       "        <td>Hadi Kermani</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_85</td>\n",
       "        <td>Great Master #7</td>\n",
       "        <td>Tonci Banov</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_86</td>\n",
       "        <td>Greatjon Umber</td>\n",
       "        <td>Clive Mantle</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_87</td>\n",
       "        <td>Gregor Clegane</td>\n",
       "        <td>Ian Whyte</td>\n",
       "        <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_87</td>\n",
       "        <td>Gregor Clegane</td>\n",
       "        <td>Conan Stevens</td>\n",
       "        <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_87</td>\n",
       "        <td>Gregor Clegane</td>\n",
       "        <td>Hafþór Júlíus Björnsson</td>\n",
       "        <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_87</td>\n",
       "        <td>Gregor Clegane</td>\n",
       "        <td>Hafþór Júlíus Björnsson</td>\n",
       "        <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_87</td>\n",
       "        <td>Gregor Clegane</td>\n",
       "        <td>Hafþór Júlíus Björnsson</td>\n",
       "        <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_87</td>\n",
       "        <td>Gregor Clegane</td>\n",
       "        <td>Hafþór Júlíus Björnsson</td>\n",
       "        <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_87</td>\n",
       "        <td>Gregor Clegane</td>\n",
       "        <td>Hafþór Júlíus Björnsson</td>\n",
       "        <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_88</td>\n",
       "        <td>Greizhen mo Ullhor</td>\n",
       "        <td>Cliff Barry</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_89</td>\n",
       "        <td>Grenn</td>\n",
       "        <td>Mark Stanley</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_91</td>\n",
       "        <td>Grey Worm</td>\n",
       "        <td>Jacob Anderson</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_92</td>\n",
       "        <td>Hallyne</td>\n",
       "        <td>Roy Dotrice</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_93</td>\n",
       "        <td>Handmaid</td>\n",
       "        <td>Sabrina Bartlett</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_94</td>\n",
       "        <td>Handmaid</td>\n",
       "        <td>Sara Dylan</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_95</td>\n",
       "        <td>Harald Karstark</td>\n",
       "        <td>Paul Rattray</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_96</td>\n",
       "        <td>Harry Strickland</td>\n",
       "        <td>Marc Rissmann</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_97</td>\n",
       "        <td>High Priestess</td>\n",
       "        <td>Souad Faress</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_98</td>\n",
       "        <td>High Septon</td>\n",
       "        <td>David Verrey</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_99</td>\n",
       "        <td>High Septon</td>\n",
       "        <td>Paul Bentley</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_100</td>\n",
       "        <td>High Sparrow</td>\n",
       "        <td>Jonathan Pryce</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_101</td>\n",
       "        <td>Hizdahr zo Loraq</td>\n",
       "        <td>Joel Fry</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_102</td>\n",
       "        <td>Hodor</td>\n",
       "        <td>Kristian Nairn</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_103</td>\n",
       "        <td>Hoster Tully</td>\n",
       "        <td>Christopher Newman</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_104</td>\n",
       "        <td>Hot Pie</td>\n",
       "        <td>Ben Hawkey</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_105</td>\n",
       "        <td>Howland Reed</td>\n",
       "        <td>Leo Woodruff</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_106</td>\n",
       "        <td>Hugh of the Vale</td>\n",
       "        <td>Jefferson Hall</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_107</td>\n",
       "        <td>Illyrio Mopatis</td>\n",
       "        <td>Roger Allam</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_108</td>\n",
       "        <td>Ilyn Payne</td>\n",
       "        <td>Wilko Johnson</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_109</td>\n",
       "        <td>Irri</td>\n",
       "        <td>Amrita Acharia</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_110</td>\n",
       "        <td>Izembaro</td>\n",
       "        <td>Richard E. Grant</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_111</td>\n",
       "        <td>Jacks</td>\n",
       "        <td>Paul Caddell</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_113</td>\n",
       "        <td>Jaime Lannister</td>\n",
       "        <td>Nikolaj Coster-Waldau</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_114</td>\n",
       "        <td>Janos Slynt</td>\n",
       "        <td>Dominic Carter</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_115</td>\n",
       "        <td>Jaqen H&#x27;ghar</td>\n",
       "        <td>Tom Wlaschiha</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_116</td>\n",
       "        <td>Jaqen&#x27;s Disguise</td>\n",
       "        <td>Patrick O&#x27;Kane</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_117</td>\n",
       "        <td>Jaremy Rykker</td>\n",
       "        <td>Jeffrey O&#x27;Brien</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_118</td>\n",
       "        <td>Jeor Mormont</td>\n",
       "        <td>James Cosmo</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_119</td>\n",
       "        <td>Jhiqui</td>\n",
       "        <td>Sarita Piotrowski</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_120</td>\n",
       "        <td>Joffrey Baratheon</td>\n",
       "        <td>Jack Gleeson</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_121</td>\n",
       "        <td>Jojen Reed</td>\n",
       "        <td>Thomas Brodie-Sangster</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_122</td>\n",
       "        <td>Jon Arryn</td>\n",
       "        <td>John Standing</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_123</td>\n",
       "        <td>Jon Snow</td>\n",
       "        <td>Kit Harington</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_124</td>\n",
       "        <td>Jonos Bracken</td>\n",
       "        <td>Gerry O&#x27;Brien</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_125</td>\n",
       "        <td>Jorah Mormont</td>\n",
       "        <td>Iain Glen</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_126</td>\n",
       "        <td>Jory Cassel</td>\n",
       "        <td>Jamie Sives</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_127</td>\n",
       "        <td>Joyeuse Erenford</td>\n",
       "        <td>Kelly Long</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_128</td>\n",
       "        <td>Karl Tanner</td>\n",
       "        <td>Burn Gorman</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_129</td>\n",
       "        <td>Karsi</td>\n",
       "        <td>Birgitte Hjort Sørensen</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_130</td>\n",
       "        <td>Kayla</td>\n",
       "        <td>Pixie Le Knot</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_131</td>\n",
       "        <td>Kegs</td>\n",
       "        <td>Tim Landers</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_132</td>\n",
       "        <td>Kevan Lannister</td>\n",
       "        <td>Ian Gelder</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_133</td>\n",
       "        <td>Khal Drogo</td>\n",
       "        <td>Jason Momoa</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_134</td>\n",
       "        <td>Khal Moro</td>\n",
       "        <td>Joseph Naufahu</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_135</td>\n",
       "        <td>Khal Rhalko</td>\n",
       "        <td>Andrei Claude</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_136</td>\n",
       "        <td>King Joffrey Baratheon Dwarf</td>\n",
       "        <td>Raymond Griffiths</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_137</td>\n",
       "        <td>King Renly Baratheon Dwarf</td>\n",
       "        <td>Dean Whatton</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_138</td>\n",
       "        <td>King Robb Stark Dwarf</td>\n",
       "        <td>George Appleby</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_139</td>\n",
       "        <td>King Stannis Baratheon Dwarf</td>\n",
       "        <td>Maxwell Laird</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_140</td>\n",
       "        <td>King&#x27;s Landing Rioter #1</td>\n",
       "        <td>Reg Wayment</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_141</td>\n",
       "        <td>King&#x27;s Landing Rioter #2</td>\n",
       "        <td>Marko Juraga</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_142</td>\n",
       "        <td>King&#x27;s Landing Rioter #3</td>\n",
       "        <td>Rea Separovic</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_143</td>\n",
       "        <td>Kinvara</td>\n",
       "        <td>Ania Bukstein</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_144</td>\n",
       "        <td>Kitty Frey</td>\n",
       "        <td>Lucy Hayes</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_145</td>\n",
       "        <td>Knight of House Frey</td>\n",
       "        <td>Patrick Ryan</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_146</td>\n",
       "        <td>Knight of House Lynderly</td>\n",
       "        <td>Paddy Rocks</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_147</td>\n",
       "        <td>Kovarro</td>\n",
       "        <td>Steven Cole</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_148</td>\n",
       "        <td>Kraznys mo Nakloz</td>\n",
       "        <td>Dan Hildebrand</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_149</td>\n",
       "        <td>Kurleket</td>\n",
       "        <td>Kevin Keenan</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_151</td>\n",
       "        <td>Lady Crane</td>\n",
       "        <td>Essie Davis</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_152</td>\n",
       "        <td>Lancel Lannister</td>\n",
       "        <td>Eugene Simon</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_153</td>\n",
       "        <td>Lannister Captain</td>\n",
       "        <td>Aiden Condron</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_154</td>\n",
       "        <td>Lannister Captain</td>\n",
       "        <td>Richard Laing</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_155</td>\n",
       "        <td>Lannister Guardsman</td>\n",
       "        <td>Conor Delaney</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_156</td>\n",
       "        <td>Lannister Messenger</td>\n",
       "        <td>Simon Stewart</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_157</td>\n",
       "        <td>Lannister Scout</td>\n",
       "        <td>Chris Gallagher</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_158</td>\n",
       "        <td>Lead Dornish Guard</td>\n",
       "        <td>Christian Vit</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_159</td>\n",
       "        <td>Leaf</td>\n",
       "        <td>Kae Alexander</td>\n",
       "        <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_159</td>\n",
       "        <td>Leaf</td>\n",
       "        <td>Octavia Selena Alexandru</td>\n",
       "        <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_160</td>\n",
       "        <td>Lem Lemoncloak</td>\n",
       "        <td>Jóhannes Haukur Jóhannesson</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_161</td>\n",
       "        <td>Leo Lefford</td>\n",
       "        <td>Vinnie McCabe</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_162</td>\n",
       "        <td>Lhara</td>\n",
       "        <td>Sarine Sofair</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_163</td>\n",
       "        <td>Little Bird</td>\n",
       "        <td>Samuel Paul Small</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_164</td>\n",
       "        <td>Little Bird</td>\n",
       "        <td>Tristan Mercieca</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_165</td>\n",
       "        <td>Little Bird #3</td>\n",
       "        <td>Michael Nevin</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_166</td>\n",
       "        <td>Little Bird #4</td>\n",
       "        <td>Jesse Magee</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_167</td>\n",
       "        <td>Little Bird #5</td>\n",
       "        <td>Iona Clarke</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_168</td>\n",
       "        <td>Little Bird #6</td>\n",
       "        <td>Lucy Gallagher</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_169</td>\n",
       "        <td>Little Bird #7</td>\n",
       "        <td>Fionn Kernan</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_170</td>\n",
       "        <td>Loboda</td>\n",
       "        <td>Zachary Baharov</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_171</td>\n",
       "        <td>Locke</td>\n",
       "        <td>Noah Taylor</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_172</td>\n",
       "        <td>Lollys Stokeworth</td>\n",
       "        <td>Elizabeth Cadwallader</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_173</td>\n",
       "        <td>Lommy Greenhands</td>\n",
       "        <td>Eros Vlahos</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_174</td>\n",
       "        <td>Loras Tyrell</td>\n",
       "        <td>Finn Jones</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_175</td>\n",
       "        <td>Lord Galbart Glover</td>\n",
       "        <td>Mark Coney</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_176</td>\n",
       "        <td>Lord Portan</td>\n",
       "        <td>Gordon Fulton</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_177</td>\n",
       "        <td>Lord Varys</td>\n",
       "        <td>Conleth Hill</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_178</td>\n",
       "        <td>Lordsport Dockhand</td>\n",
       "        <td>Jer O&#x27;Leary</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_179</td>\n",
       "        <td>Lothar Frey</td>\n",
       "        <td>Daniel Tuite</td>\n",
       "        <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_179</td>\n",
       "        <td>Lothar Frey</td>\n",
       "        <td>Tom Brooke</td>\n",
       "        <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_180</td>\n",
       "        <td>Loyal Night&#x27;s Watchman #1</td>\n",
       "        <td>Steve Cash</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_181</td>\n",
       "        <td>Loyal Night&#x27;s Watchman #2</td>\n",
       "        <td>Robert Fawsitt</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_182</td>\n",
       "        <td>Lyanna Mormont</td>\n",
       "        <td>Bella Ramsey</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_183</td>\n",
       "        <td>Lyanna Stark</td>\n",
       "        <td>Aisling Franciosi</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_184</td>\n",
       "        <td>Lysa Arryn</td>\n",
       "        <td>Kate Dickie</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_185</td>\n",
       "        <td>Mace Tyrell</td>\n",
       "        <td>Roger Ashton-Griffiths</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_186</td>\n",
       "        <td>Maester Aemon</td>\n",
       "        <td>Peter Vaughan</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_187</td>\n",
       "        <td>Maester Cressen</td>\n",
       "        <td>Oliver Ford Davies</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_188</td>\n",
       "        <td>Maester Luwin</td>\n",
       "        <td>Donald Sumpter</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_189</td>\n",
       "        <td>Maester Wolkan</td>\n",
       "        <td>Richard Rycroft</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_190</td>\n",
       "        <td>Mag the Mighty</td>\n",
       "        <td>Neil Fingleton</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_191</td>\n",
       "        <td>Maggy</td>\n",
       "        <td>Jodhi May</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_192</td>\n",
       "        <td>Mago</td>\n",
       "        <td>Ivailo Dimitrov</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_193</td>\n",
       "        <td>Male Prostitute</td>\n",
       "        <td>Dennis Stokes</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_194</td>\n",
       "        <td>Malko</td>\n",
       "        <td>Adewale Akinnuoye-Agbaje</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_195</td>\n",
       "        <td>Mance Rayder</td>\n",
       "        <td>Ciarán Hinds</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_196</td>\n",
       "        <td>Mandon Moore</td>\n",
       "        <td>James Doran</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_197</td>\n",
       "        <td>Manservant</td>\n",
       "        <td>Conor Watters</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_198</td>\n",
       "        <td>Marei</td>\n",
       "        <td>Josephine Gillan</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_199</td>\n",
       "        <td>Margaery Tyrell</td>\n",
       "        <td>Natalie Dormer</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_200</td>\n",
       "        <td>Margaery&#x27;s Handmaiden</td>\n",
       "        <td>Aisling Jarrett-Gavin</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_201</td>\n",
       "        <td>Marillion</td>\n",
       "        <td>Emun Elliott</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_202</td>\n",
       "        <td>Martyn Lannister</td>\n",
       "        <td>Dean-Charles Chapman</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_203</td>\n",
       "        <td>Masha Heddle</td>\n",
       "        <td>Susie Kelly</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_204</td>\n",
       "        <td>Matthos Seaworth</td>\n",
       "        <td>Kerr Logan</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_205</td>\n",
       "        <td>Meera Reed</td>\n",
       "        <td>Ellie Kendrick</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_206</td>\n",
       "        <td>Meereen Slave Master</td>\n",
       "        <td>Laurence Doherty</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_207</td>\n",
       "        <td>Melessa Tarly</td>\n",
       "        <td>Samantha Spiro</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_208</td>\n",
       "        <td>Melisandre</td>\n",
       "        <td>Carice van Houten</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_209</td>\n",
       "        <td>Merchant Captain</td>\n",
       "        <td>Gary Pillai</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_210</td>\n",
       "        <td>Mero</td>\n",
       "        <td>Mark Killeen</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_211</td>\n",
       "        <td>Meryn Trant</td>\n",
       "        <td>Ian Beattie</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_212</td>\n",
       "        <td>Mhaegen</td>\n",
       "        <td>Antonia Christophers</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_213</td>\n",
       "        <td>Mikken</td>\n",
       "        <td>Boyd Rankin</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_214</td>\n",
       "        <td>Mirelle</td>\n",
       "        <td>Elisa Lasowski</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_215</td>\n",
       "        <td>Mirri Maz Duur</td>\n",
       "        <td>Mia Soteriou</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_216</td>\n",
       "        <td>Missandei</td>\n",
       "        <td>Nathalie Emmanuel</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_217</td>\n",
       "        <td>Mord</td>\n",
       "        <td>Ciaran Bermingham</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_218</td>\n",
       "        <td>Mossador</td>\n",
       "        <td>Reece Noi</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_219</td>\n",
       "        <td>Musician #1</td>\n",
       "        <td>Nanna Bryndís Hilmarsdóttir</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_220</td>\n",
       "        <td>Musician #1</td>\n",
       "        <td>Jon Thor Birgisson</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_221</td>\n",
       "        <td>Musician #2</td>\n",
       "        <td>Ragnar Þórhallsson</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_222</td>\n",
       "        <td>Musician #2</td>\n",
       "        <td>Orri P. Dyrason</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_223</td>\n",
       "        <td>Musician #3</td>\n",
       "        <td>Georg Holm</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_224</td>\n",
       "        <td>Musician #3</td>\n",
       "        <td>Arnar Rosenkranz Hilmarsson</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_225</td>\n",
       "        <td>Musician #4</td>\n",
       "        <td>Brynjar Leifsson</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_226</td>\n",
       "        <td>Musician #5</td>\n",
       "        <td>Kristján Kristjánsson</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_227</td>\n",
       "        <td>Mycah</td>\n",
       "        <td>Rhodri Hosking</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_228</td>\n",
       "        <td>Myranda</td>\n",
       "        <td>Charlotte Hope</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_229</td>\n",
       "        <td>Myrcella Baratheon</td>\n",
       "        <td>Aimee Richardson</td>\n",
       "        <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_229</td>\n",
       "        <td>Myrcella Baratheon</td>\n",
       "        <td>Aimee Richardson</td>\n",
       "        <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_229</td>\n",
       "        <td>Myrcella Baratheon</td>\n",
       "        <td>Nell Tiger Free</td>\n",
       "        <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_229</td>\n",
       "        <td>Myrcella Baratheon</td>\n",
       "        <td>Nell Tiger Free</td>\n",
       "        <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_230</td>\n",
       "        <td>Ned Umber</td>\n",
       "        <td>Harry Grasby</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_231</td>\n",
       "        <td>Night&#x27;s Watch Deserter</td>\n",
       "        <td>Barry John O&#x27;Connor</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_232</td>\n",
       "        <td>Night&#x27;s Watch Messenger</td>\n",
       "        <td>Frank O&#x27;Sullivan</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_233</td>\n",
       "        <td>Night&#x27;s Watch Officer</td>\n",
       "        <td>Dennis McKeever</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_234</td>\n",
       "        <td>Night&#x27;s Watch Officer</td>\n",
       "        <td>Michael Stuart</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_235</td>\n",
       "        <td>Night&#x27;s Watchman</td>\n",
       "        <td>Marcus Lamb</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_236</td>\n",
       "        <td>Night&#x27;s Watchman</td>\n",
       "        <td>Michael Hooley</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_237</td>\n",
       "        <td>Night&#x27;s Watchman</td>\n",
       "        <td>Patrick J Molloy</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_238</td>\n",
       "        <td>Night&#x27;s Watchman #1</td>\n",
       "        <td>Michael Hayes</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_239</td>\n",
       "        <td>Night&#x27;s Watchman #2</td>\n",
       "        <td>Ruairí Heading</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_240</td>\n",
       "        <td>Night&#x27;s Watchman #2</td>\n",
       "        <td>Tony Flynn</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_241</td>\n",
       "        <td>Northman Rioter</td>\n",
       "        <td>Jed Murray</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_243</td>\n",
       "        <td>Nymeria Sand</td>\n",
       "        <td>Jessica Henwick</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_244</td>\n",
       "        <td>Obara Sand</td>\n",
       "        <td>Keisha Castle-Hughes</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_245</td>\n",
       "        <td>Oberyn Martell</td>\n",
       "        <td>Pedro Pascal</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_246</td>\n",
       "        <td>Old Nan</td>\n",
       "        <td>Margaret John</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_247</td>\n",
       "        <td>Old Woman Prisoner</td>\n",
       "        <td>Gina Moxley</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_248</td>\n",
       "        <td>Olenna Tyrell</td>\n",
       "        <td>Diana Rigg</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_249</td>\n",
       "        <td>Olly</td>\n",
       "        <td>Brenock O&#x27;Connor</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_250</td>\n",
       "        <td>Olyvar</td>\n",
       "        <td>Will Tudor</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_251</td>\n",
       "        <td>Orell</td>\n",
       "        <td>Mackenzie Crook</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_252</td>\n",
       "        <td>Ornela</td>\n",
       "        <td>Hannah John-Kamen</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_253</td>\n",
       "        <td>Osha</td>\n",
       "        <td>Natalia Tena</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_254</td>\n",
       "        <td>Othell Yarwyck</td>\n",
       "        <td>Brian Fortune</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_256</td>\n",
       "        <td>Pentoshi Servant</td>\n",
       "        <td>Rania Zouari</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_257</td>\n",
       "        <td>Petyr Baelish</td>\n",
       "        <td>Aidan Gillen</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_258</td>\n",
       "        <td>Podrick Payne</td>\n",
       "        <td>Daniel Portman</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_259</td>\n",
       "        <td>Polliver</td>\n",
       "        <td>Andy Kellegher</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_260</td>\n",
       "        <td>Prendahl na Ghezn</td>\n",
       "        <td>Ramon Tikaram</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_261</td>\n",
       "        <td>Pyat Pree</td>\n",
       "        <td>Ian Hanmore</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_262</td>\n",
       "        <td>Pypar</td>\n",
       "        <td>Josef Altin</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_263</td>\n",
       "        <td>Qhono</td>\n",
       "        <td>Staz Nair</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_264</td>\n",
       "        <td>Qhorin Halfhand</td>\n",
       "        <td>Simon Armstrong</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_265</td>\n",
       "        <td>Qotho</td>\n",
       "        <td>Dar Salim</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_266</td>\n",
       "        <td>Quaithe</td>\n",
       "        <td>Laura Pradelska</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_267</td>\n",
       "        <td>Qyburn</td>\n",
       "        <td>Anton Lesser</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_268</td>\n",
       "        <td>Rakharo</td>\n",
       "        <td>Elyes Gabel</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_269</td>\n",
       "        <td>Ramsay Snow</td>\n",
       "        <td>Iwan Rheon</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_270</td>\n",
       "        <td>Randyll Tarly</td>\n",
       "        <td>James Faulkner</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_271</td>\n",
       "        <td>Rast</td>\n",
       "        <td>Luke Barnes</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_272</td>\n",
       "        <td>Rattleshirt</td>\n",
       "        <td>Edward Dogliani</td>\n",
       "        <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_272</td>\n",
       "        <td>Rattleshirt</td>\n",
       "        <td>Edward Dogliani</td>\n",
       "        <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_272</td>\n",
       "        <td>Rattleshirt</td>\n",
       "        <td>Ross O&#x27;Hennessy</td>\n",
       "        <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_273</td>\n",
       "        <td>Razdal mo Eraz</td>\n",
       "        <td>George Georgiou</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_274</td>\n",
       "        <td>Red Keep Stableboy</td>\n",
       "        <td>Hugo Culverhouse</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_275</td>\n",
       "        <td>Red Priestess</td>\n",
       "        <td>Melanie Liburd</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_276</td>\n",
       "        <td>Red Priestess</td>\n",
       "        <td>Rila Fukushima</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_277</td>\n",
       "        <td>Renly Baratheon</td>\n",
       "        <td>Gethin Anthony</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_283</td>\n",
       "        <td>Rickard Karstark</td>\n",
       "        <td>John Stahl</td>\n",
       "        <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_283</td>\n",
       "        <td>Rickard Karstark</td>\n",
       "        <td>John Stahl</td>\n",
       "        <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_283</td>\n",
       "        <td>Rickard Karstark</td>\n",
       "        <td>Steve Blount</td>\n",
       "        <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_285</td>\n",
       "        <td>Rickon Stark</td>\n",
       "        <td>Art Parkinson</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_286</td>\n",
       "        <td>Robb Stark</td>\n",
       "        <td>Richard Madden</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_287</td>\n",
       "        <td>Robert Baratheon</td>\n",
       "        <td>Mark Addy</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_288</td>\n",
       "        <td>Robett Glover</td>\n",
       "        <td>Tim McInnerny</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_289</td>\n",
       "        <td>Robin Arryn</td>\n",
       "        <td>Lino Facioli</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_290</td>\n",
       "        <td>Rodrik Cassel</td>\n",
       "        <td>Ron Donachie</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_291</td>\n",
       "        <td>Roose Bolton</td>\n",
       "        <td>Michael McElhatton</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_292</td>\n",
       "        <td>Rorge</td>\n",
       "        <td>Andy Beckwith</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_293</td>\n",
       "        <td>Ros</td>\n",
       "        <td>Esmé Bianco</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_294</td>\n",
       "        <td>Roslin Frey</td>\n",
       "        <td>Alexandra Dowling</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_295</td>\n",
       "        <td>Royal Steward</td>\n",
       "        <td>Robert Sterne</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_296</td>\n",
       "        <td>Ryger Rivers</td>\n",
       "        <td>Bryan McCaugherty</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_297</td>\n",
       "        <td>Salladhor Saan</td>\n",
       "        <td>Lucian Msamati</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_298</td>\n",
       "        <td>Samwell Tarly</td>\n",
       "        <td>John Bradley</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_299</td>\n",
       "        <td>Sandor Clegane</td>\n",
       "        <td>Rory McCann</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_300</td>\n",
       "        <td>Sansa Stark</td>\n",
       "        <td>Sophie Turner</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_301</td>\n",
       "        <td>Selyse Baratheon</td>\n",
       "        <td>Tara Fitzgerald</td>\n",
       "        <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_301</td>\n",
       "        <td>Selyse Baratheon</td>\n",
       "        <td>Tara Fitzgerald</td>\n",
       "        <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_301</td>\n",
       "        <td>Selyse Baratheon</td>\n",
       "        <td>Tara Fitzgerald</td>\n",
       "        <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_301</td>\n",
       "        <td>Selyse Baratheon</td>\n",
       "        <td>Sarah MacKeever</td>\n",
       "        <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_302</td>\n",
       "        <td>Septa Moelle</td>\n",
       "        <td>Maggie Hayes</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_303</td>\n",
       "        <td>Septa Mordane</td>\n",
       "        <td>Susan Brown</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_304</td>\n",
       "        <td>Septa Scolera</td>\n",
       "        <td>Mary Jordan</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_305</td>\n",
       "        <td>Septa Unella</td>\n",
       "        <td>Hannah Waddingham</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_306</td>\n",
       "        <td>Septon</td>\n",
       "        <td>Kenneth Hadley</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_307</td>\n",
       "        <td>Septon</td>\n",
       "        <td>Terry Byrne</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_308</td>\n",
       "        <td>Ser Endrew Tarth</td>\n",
       "        <td>Dez McMahon</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_309</td>\n",
       "        <td>Shae</td>\n",
       "        <td>Sibel Kekilli</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_310</td>\n",
       "        <td>Shagga</td>\n",
       "        <td>Mark Lewis Jones</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_312</td>\n",
       "        <td>Shireen Baratheon</td>\n",
       "        <td>Kerry Ingram</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_313</td>\n",
       "        <td>Silk King</td>\n",
       "        <td>Slavko Juraga</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_314</td>\n",
       "        <td>Smalljon Umber</td>\n",
       "        <td>Dean S. Jagger</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_315</td>\n",
       "        <td>Spice King</td>\n",
       "        <td>Nicholas Blane</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_316</td>\n",
       "        <td>Stannis Baratheon</td>\n",
       "        <td>Stephen Dillane</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_317</td>\n",
       "        <td>Stark Bannerman</td>\n",
       "        <td>Michael Stuart</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_318</td>\n",
       "        <td>Stark Guard</td>\n",
       "        <td>Faolan Morgan</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_319</td>\n",
       "        <td>Stark Guard</td>\n",
       "        <td>Rick Burn</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_320</td>\n",
       "        <td>Stark Guard</td>\n",
       "        <td>David Sheehan</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_321</td>\n",
       "        <td>Stark Guard</td>\n",
       "        <td>Paul Monahan</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_322</td>\n",
       "        <td>Steelshanks Walton</td>\n",
       "        <td>Jamie Michie</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_323</td>\n",
       "        <td>Stevron Frey</td>\n",
       "        <td>Colin Carnegie</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_324</td>\n",
       "        <td>Stiv</td>\n",
       "        <td>Stephen Don</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_325</td>\n",
       "        <td>Styr</td>\n",
       "        <td>Yuri Kolokolnikov</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_327</td>\n",
       "        <td>Syrio Forel</td>\n",
       "        <td>Miltos Yerolemou</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_328</td>\n",
       "        <td>Talisa Maegyr</td>\n",
       "        <td>Oona Chaplin</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_329</td>\n",
       "        <td>Talla Tarly</td>\n",
       "        <td>Rebecca Benson</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_330</td>\n",
       "        <td>Ternesio Terys</td>\n",
       "        <td>Gary Oliver</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_331</td>\n",
       "        <td>The Bear</td>\n",
       "        <td>Bart the Bear</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_332</td>\n",
       "        <td>The Night King</td>\n",
       "        <td>Richard Brake</td>\n",
       "        <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_332</td>\n",
       "        <td>The Night King</td>\n",
       "        <td>Richard Brake</td>\n",
       "        <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_332</td>\n",
       "        <td>The Night King</td>\n",
       "        <td>Vladimir &#x27;Furdo&#x27; Furdik</td>\n",
       "        <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_332</td>\n",
       "        <td>The Night King</td>\n",
       "        <td>Vladimir &#x27;Furdo&#x27; Furdik</td>\n",
       "        <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_332</td>\n",
       "        <td>The Night King</td>\n",
       "        <td>Vladimir &#x27;Furdo&#x27; Furdik</td>\n",
       "        <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_333</td>\n",
       "        <td>The Tickler</td>\n",
       "        <td>Anthony Morris</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_334</td>\n",
       "        <td>The Waif</td>\n",
       "        <td>Faye Marsay</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_335</td>\n",
       "        <td>Thenn Warg</td>\n",
       "        <td>Joseph Gatt</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_336</td>\n",
       "        <td>Theon Greyjoy</td>\n",
       "        <td>Alfie Allen</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_337</td>\n",
       "        <td>Thin Man</td>\n",
       "        <td>Oengus MacNamara</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_338</td>\n",
       "        <td>Thoros of Myr</td>\n",
       "        <td>Paul Kaye</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_339</td>\n",
       "        <td>Three-Eyed Raven</td>\n",
       "        <td>Struan Rodger</td>\n",
       "        <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_339</td>\n",
       "        <td>Three-Eyed Raven</td>\n",
       "        <td>Max von Sydow</td>\n",
       "        <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_340</td>\n",
       "        <td>Timett</td>\n",
       "        <td>Tobias Winter</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_341</td>\n",
       "        <td>Tobho Mott</td>\n",
       "        <td>Andrew Wilde</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_342</td>\n",
       "        <td>Todder</td>\n",
       "        <td>Will O&#x27;Connell</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_343</td>\n",
       "        <td>Tomard</td>\n",
       "        <td>Phil Dixon</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_344</td>\n",
       "        <td>Tommen Baratheon</td>\n",
       "        <td>Callum Wharry</td>\n",
       "        <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_344</td>\n",
       "        <td>Tommen Baratheon</td>\n",
       "        <td>Callum Wharry</td>\n",
       "        <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_344</td>\n",
       "        <td>Tommen Baratheon</td>\n",
       "        <td>Dean-Charles Chapman</td>\n",
       "        <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_344</td>\n",
       "        <td>Tommen Baratheon</td>\n",
       "        <td>Dean-Charles Chapman</td>\n",
       "        <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_344</td>\n",
       "        <td>Tommen Baratheon</td>\n",
       "        <td>Dean-Charles Chapman</td>\n",
       "        <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_345</td>\n",
       "        <td>Tormund Giantsbane</td>\n",
       "        <td>Kristofer Hivju</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_346</td>\n",
       "        <td>Trystane Martell</td>\n",
       "        <td>Toby Sebastian</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_347</td>\n",
       "        <td>Tycho Nestoris</td>\n",
       "        <td>Mark Gatiss</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_348</td>\n",
       "        <td>Tyene Sand</td>\n",
       "        <td>Rosabell Laurenti Sellers</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_349</td>\n",
       "        <td>Tyrion Lannister</td>\n",
       "        <td>Peter Dinklage</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_350</td>\n",
       "        <td>Tywin Lannister</td>\n",
       "        <td>Charles Dance</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_351</td>\n",
       "        <td>Vala</td>\n",
       "        <td>Meena Rayann</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_352</td>\n",
       "        <td>Vardis Egen</td>\n",
       "        <td>Brendan McCormack</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_353</td>\n",
       "        <td>Varly</td>\n",
       "        <td>Graham Charles</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_355</td>\n",
       "        <td>Viserys Targaryen</td>\n",
       "        <td>Harry Lloyd</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_356</td>\n",
       "        <td>Walda Bolton</td>\n",
       "        <td>Elizabeth Webster</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_357</td>\n",
       "        <td>Walder Frey</td>\n",
       "        <td>David Bradley</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_358</td>\n",
       "        <td>Wallen</td>\n",
       "        <td>Barry O&#x27;Connor</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_359</td>\n",
       "        <td>Warlock</td>\n",
       "        <td>Lottie Steer</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_360</td>\n",
       "        <td>Waymar Royce</td>\n",
       "        <td>Rob Ostlere</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_361</td>\n",
       "        <td>Wedding Band</td>\n",
       "        <td>Sigur Rós</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_362</td>\n",
       "        <td>White Walker</td>\n",
       "        <td>Ian Whyte</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_363</td>\n",
       "        <td>White Walker</td>\n",
       "        <td>Ross Mullan</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_364</td>\n",
       "        <td>White Walker</td>\n",
       "        <td>Tim Loane</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_365</td>\n",
       "        <td>White Walker #2</td>\n",
       "        <td>Spencer Wilding</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_366</td>\n",
       "        <td>Wight Wildling Girl</td>\n",
       "        <td>Claire Wright</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_367</td>\n",
       "        <td>Wildling Rioter</td>\n",
       "        <td>Michael Patrick</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_368</td>\n",
       "        <td>Will</td>\n",
       "        <td>Bronson Webb</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_369</td>\n",
       "        <td>Willem Lannister</td>\n",
       "        <td>Timothy Gibbons</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_370</td>\n",
       "        <td>Willis Wode</td>\n",
       "        <td>Ryan McKenna</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_371</td>\n",
       "        <td>Wine Merchant</td>\n",
       "        <td>Simon Lowe</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_372</td>\n",
       "        <td>Winterfell Shepherd</td>\n",
       "        <td>Donagh Deeney</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_373</td>\n",
       "        <td>Wun Wun</td>\n",
       "        <td>Ian Whyte</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_374</td>\n",
       "        <td>Wyllis</td>\n",
       "        <td>Sam Coleman</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_375</td>\n",
       "        <td>Xaro Xhoan Daxos</td>\n",
       "        <td>Nonso Anozie</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_376</td>\n",
       "        <td>Yara Greyjoy</td>\n",
       "        <td>Gemma Whelan</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_377</td>\n",
       "        <td>Yezzan zo Qaggaz</td>\n",
       "        <td>Enzo Cilenti</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_378</td>\n",
       "        <td>Ygritte</td>\n",
       "        <td>Rose Leslie</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_379</td>\n",
       "        <td>Yohn Royce</td>\n",
       "        <td>Rupert Vansittart</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_380</td>\n",
       "        <td>Yoren</td>\n",
       "        <td>Francis Magee</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_381</td>\n",
       "        <td>Young Benjen Stark</td>\n",
       "        <td>Matteo Elezi</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_382</td>\n",
       "        <td>Young Cersei Lannister</td>\n",
       "        <td>Nell Williams</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_383</td>\n",
       "        <td>Young Lyanna Stark</td>\n",
       "        <td>Cordelia Hill</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_384</td>\n",
       "        <td>Young Nan</td>\n",
       "        <td>Annette Tierney</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_385</td>\n",
       "        <td>Young Ned</td>\n",
       "        <td>Robert Aramayo</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_386</td>\n",
       "        <td>Young Ned Stark</td>\n",
       "        <td>Sebastian Croft</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_387</td>\n",
       "        <td>Young Rodrik Cassel</td>\n",
       "        <td>Fergus Leathem</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>CH_388</td>\n",
       "        <td>Zanrush</td>\n",
       "        <td>Gerald Lepkowski</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('CH_0', 'Addam Marbrand', 'B.J. Hogg', ''),\n",
       " ('CH_2', 'Aeron Greyjoy', 'Michael Feast', ''),\n",
       " ('CH_3', 'Aerys II Targaryen', 'David Rintoul', ''),\n",
       " ('CH_4', 'Akho', 'Chuku Modu', ''),\n",
       " ('CH_5', 'Alliser Thorne', 'Owen Teale', ''),\n",
       " ('CH_6', 'Alton Lannister', 'Karl Davies', ''),\n",
       " ('CH_7', 'Alys Karstark', 'Megan Parkinson', ''),\n",
       " ('CH_8', 'Amory Lorch', 'Fintan McKeown', ''),\n",
       " ('CH_9', 'Anguy', 'Philip McGinley', ''),\n",
       " ('CH_10', 'Archmaester Marwyn', 'Jim Broadbent', ''),\n",
       " ('CH_11', 'Areo Hotah', 'Deobia Oparei', ''),\n",
       " ('CH_12', 'Armeca', 'Sahara Knite', ''),\n",
       " ('CH_13', 'Arthur', 'Nathanael Saleh', ''),\n",
       " ('CH_14', 'Arthur Dayne', 'Luke Roberts', ''),\n",
       " ('CH_15', 'Arya Stark', 'Maisie Williams', ''),\n",
       " ('CH_16', 'Baby Sam', 'William Wilson', '7.0'),\n",
       " ('CH_16', 'Baby Sam', 'James Wilson', '7.0'),\n",
       " ('CH_17', 'Balon Greyjoy', 'Patrick Malahide', ''),\n",
       " ('CH_18', 'Baratheon Guard', 'Phil Barnhill', ''),\n",
       " ('CH_19', 'Barristan Selmy', 'Ian McElhinney', ''),\n",
       " ('CH_20', 'Benjen Stark', 'Joseph Mawle', ''),\n",
       " ('CH_21', 'Beric Dondarrion', 'Richard Dormer', '3.0'),\n",
       " ('CH_21', 'Beric Dondarrion', 'Richard Dormer', '6.0'),\n",
       " ('CH_21', 'Beric Dondarrion', 'Richard Dormer', '7.0'),\n",
       " ('CH_21', 'Beric Dondarrion', 'Richard Dormer', '8.0'),\n",
       " ('CH_21', 'Beric Dondarrion', 'David Michael Scott', '1.0'),\n",
       " ('CH_22', 'Bianca', 'Eline Powell', ''),\n",
       " ('CH_23', 'Biter', 'Gerard Jordan', ''),\n",
       " ('CH_24', 'Black Lorren', 'Forbes KB', ''),\n",
       " ('CH_25', 'Black Walder Rivers', 'Tim Plester', ''),\n",
       " ('CH_26', 'Bobono', 'Leigh Gill', ''),\n",
       " ('CH_27', 'Bowen Marsh', 'Michael Condron', ''),\n",
       " ('CH_29', 'Bran Stark', 'Isaac Hempstead Wright', ''),\n",
       " ('CH_30', 'Brea', 'Gemita Samarra', ''),\n",
       " ('CH_31', 'Brienne of Tarth', 'Gwendoline Christie', ''),\n",
       " ('CH_32', 'Bronn', 'Jerome Flynn', ''),\n",
       " ('CH_33', 'Brother Ray', 'Ian McShane', ''),\n",
       " ('CH_34', 'Brynden Tully', 'Clive Russell', ''),\n",
       " ('CH_35', 'Captain of the Archers', 'Michael Grennell', ''),\n",
       " ('CH_36', 'Catelyn Stark', 'Michelle Fairley', ''),\n",
       " ('CH_37', 'Catspaw Assassin', 'Lalor Roddy', ''),\n",
       " ('CH_38', 'Cersei Lannister', 'Lena Headey', ''),\n",
       " ('CH_39', 'Child of the Forest', 'Alice Hewkin', ''),\n",
       " ('CH_40', 'Citadel Maester', 'Frank Hvam', ''),\n",
       " ('CH_41', 'Colen of Greenpools', 'Darren Killeen', ''),\n",
       " ('CH_42', 'Craster', 'Robert Pugh', ''),\n",
       " ('CH_43', 'Daario Naharis', 'Ed Skrein', '3.0'),\n",
       " ('CH_43', 'Daario Naharis', 'Michiel Huisman', '4.0'),\n",
       " ('CH_43', 'Daario Naharis', 'Michiel Huisman', '5.0'),\n",
       " ('CH_43', 'Daario Naharis', 'Michiel Huisman', '6.0'),\n",
       " ('CH_44', 'Daenerys Targaryen', 'Emilia Clarke', ''),\n",
       " ('CH_45', 'Dagmer Cleftjaw', 'Ralph Ineson', ''),\n",
       " ('CH_46', 'Daisy', 'Maisie Dee', ''),\n",
       " ('CH_47', 'Davos Seaworth', 'Liam Cunningham', ''),\n",
       " ('CH_48', 'Dickon Tarly', 'Freddie Stroma', '6.0'),\n",
       " ('CH_48', 'Dickon Tarly', 'Tom Hopper', '7.0'),\n",
       " ('CH_49', 'Dongo the Giant', 'Ian Whyte', ''),\n",
       " ('CH_50', 'Donnel Waynwood', 'Alisdair Simpson', ''),\n",
       " ('CH_51', 'Dontos Hollard', 'Tony Way', ''),\n",
       " ('CH_52', 'Doran Martell', 'Alexander Siddig', ''),\n",
       " ('CH_53', 'Doreah', 'Roxanne McKee', ''),\n",
       " ('CH_54', 'Dothraki Bloodrider #1', 'Diogo Sales', ''),\n",
       " ('CH_55', 'Dothraki Bloodrider #2', 'Junade Khan', ''),\n",
       " ('CH_56', 'Drennan', 'David Coakley', ''),\n",
       " ('CH_58', 'Drowned Priest', 'Jonathan Ryan', ''),\n",
       " ('CH_59', 'Eddard Stark', 'Sean Bean', ''),\n",
       " ('CH_60', 'Eddison Tollett', 'Ben Crompton', ''),\n",
       " ('CH_61', 'Edmure Tully', 'Tobias Menzies', ''),\n",
       " ('CH_62', 'Ellaria Sand', 'Indira Varma', ''),\n",
       " ('CH_64', 'Eon Hunter', 'Barrington Cullen', ''),\n",
       " ('CH_65', 'Euron Greyjoy', 'Pilou Asbæk', ''),\n",
       " ('CH_66', 'Faceless Man', 'Cedric Henderson', ''),\n",
       " ('CH_67', 'Farlen', 'Peter Ballance', ''),\n",
       " ('CH_68', 'Gared', 'Dermot Keaney', ''),\n",
       " ('CH_69', 'Gatins', 'Ricky Champ', ''),\n",
       " ('CH_70', 'Gendry', 'Joe Dempsie', ''),\n",
       " ('CH_71', 'Gerold Hightower', 'Eddie Eyre', ''),\n",
       " ('CH_73', 'Gilly', 'Hannah Murray', ''),\n",
       " ('CH_74', 'Goldcloak', 'Alan Paris', ''),\n",
       " ('CH_75', 'Goldcloak', 'Sam Callis', ''),\n",
       " ('CH_76', 'Goldcloak #1', 'Nikovich Sammut', ''),\n",
       " ('CH_77', 'Goldcloak #2', 'Seamus Kelly', ''),\n",
       " ('CH_78', 'Grand Maester Pycelle', 'Julian Glover', ''),\n",
       " ('CH_79', 'Great Master #1', 'Emilio Doorgasingh', '4.0'),\n",
       " ('CH_79', 'Great Master #1', 'Gianpiero Cognoli', '5.0'),\n",
       " ('CH_80', 'Great Master #2', 'Derek Horsham', '4.0'),\n",
       " ('CH_80', 'Great Master #2', 'Bobby Asghar', '5.0'),\n",
       " ('CH_81', 'Great Master #3', 'Peter Silverleaf', ''),\n",
       " ('CH_82', 'Great Master #4', 'Davor Jozinovic', ''),\n",
       " ('CH_83', 'Great Master #5', 'Ivan Peric', ''),\n",
       " ('CH_84', 'Great Master #6', 'Hadi Kermani', ''),\n",
       " ('CH_85', 'Great Master #7', 'Tonci Banov', ''),\n",
       " ('CH_86', 'Greatjon Umber', 'Clive Mantle', ''),\n",
       " ('CH_87', 'Gregor Clegane', 'Ian Whyte', '2.0'),\n",
       " ('CH_87', 'Gregor Clegane', 'Conan Stevens', '1.0'),\n",
       " ('CH_87', 'Gregor Clegane', 'Hafþór Júlíus Björnsson', '4.0'),\n",
       " ('CH_87', 'Gregor Clegane', 'Hafþór Júlíus Björnsson', '5.0'),\n",
       " ('CH_87', 'Gregor Clegane', 'Hafþór Júlíus Björnsson', '6.0'),\n",
       " ('CH_87', 'Gregor Clegane', 'Hafþór Júlíus Björnsson', '7.0'),\n",
       " ('CH_87', 'Gregor Clegane', 'Hafþór Júlíus Björnsson', '8.0'),\n",
       " ('CH_88', 'Greizhen mo Ullhor', 'Cliff Barry', ''),\n",
       " ('CH_89', 'Grenn', 'Mark Stanley', ''),\n",
       " ('CH_91', 'Grey Worm', 'Jacob Anderson', ''),\n",
       " ('CH_92', 'Hallyne', 'Roy Dotrice', ''),\n",
       " ('CH_93', 'Handmaid', 'Sabrina Bartlett', ''),\n",
       " ('CH_94', 'Handmaid', 'Sara Dylan', ''),\n",
       " ('CH_95', 'Harald Karstark', 'Paul Rattray', ''),\n",
       " ('CH_96', 'Harry Strickland', 'Marc Rissmann', ''),\n",
       " ('CH_97', 'High Priestess', 'Souad Faress', ''),\n",
       " ('CH_98', 'High Septon', 'David Verrey', ''),\n",
       " ('CH_99', 'High Septon', 'Paul Bentley', ''),\n",
       " ('CH_100', 'High Sparrow', 'Jonathan Pryce', ''),\n",
       " ('CH_101', 'Hizdahr zo Loraq', 'Joel Fry', ''),\n",
       " ('CH_102', 'Hodor', 'Kristian Nairn', ''),\n",
       " ('CH_103', 'Hoster Tully', 'Christopher Newman', ''),\n",
       " ('CH_104', 'Hot Pie', 'Ben Hawkey', ''),\n",
       " ('CH_105', 'Howland Reed', 'Leo Woodruff', ''),\n",
       " ('CH_106', 'Hugh of the Vale', 'Jefferson Hall', ''),\n",
       " ('CH_107', 'Illyrio Mopatis', 'Roger Allam', ''),\n",
       " ('CH_108', 'Ilyn Payne', 'Wilko Johnson', ''),\n",
       " ('CH_109', 'Irri', 'Amrita Acharia', ''),\n",
       " ('CH_110', 'Izembaro', 'Richard E. Grant', ''),\n",
       " ('CH_111', 'Jacks', 'Paul Caddell', ''),\n",
       " ('CH_113', 'Jaime Lannister', 'Nikolaj Coster-Waldau', ''),\n",
       " ('CH_114', 'Janos Slynt', 'Dominic Carter', ''),\n",
       " ('CH_115', \"Jaqen H'ghar\", 'Tom Wlaschiha', ''),\n",
       " ('CH_116', \"Jaqen's Disguise\", \"Patrick O'Kane\", ''),\n",
       " ('CH_117', 'Jaremy Rykker', \"Jeffrey O'Brien\", ''),\n",
       " ('CH_118', 'Jeor Mormont', 'James Cosmo', ''),\n",
       " ('CH_119', 'Jhiqui', 'Sarita Piotrowski', ''),\n",
       " ('CH_120', 'Joffrey Baratheon', 'Jack Gleeson', ''),\n",
       " ('CH_121', 'Jojen Reed', 'Thomas Brodie-Sangster', ''),\n",
       " ('CH_122', 'Jon Arryn', 'John Standing', ''),\n",
       " ('CH_123', 'Jon Snow', 'Kit Harington', ''),\n",
       " ('CH_124', 'Jonos Bracken', \"Gerry O'Brien\", ''),\n",
       " ('CH_125', 'Jorah Mormont', 'Iain Glen', ''),\n",
       " ('CH_126', 'Jory Cassel', 'Jamie Sives', ''),\n",
       " ('CH_127', 'Joyeuse Erenford', 'Kelly Long', ''),\n",
       " ('CH_128', 'Karl Tanner', 'Burn Gorman', ''),\n",
       " ('CH_129', 'Karsi', 'Birgitte Hjort Sørensen', ''),\n",
       " ('CH_130', 'Kayla', 'Pixie Le Knot', ''),\n",
       " ('CH_131', 'Kegs', 'Tim Landers', ''),\n",
       " ('CH_132', 'Kevan Lannister', 'Ian Gelder', ''),\n",
       " ('CH_133', 'Khal Drogo', 'Jason Momoa', ''),\n",
       " ('CH_134', 'Khal Moro', 'Joseph Naufahu', ''),\n",
       " ('CH_135', 'Khal Rhalko', 'Andrei Claude', ''),\n",
       " ('CH_136', 'King Joffrey Baratheon Dwarf', 'Raymond Griffiths', ''),\n",
       " ('CH_137', 'King Renly Baratheon Dwarf', 'Dean Whatton', ''),\n",
       " ('CH_138', 'King Robb Stark Dwarf', 'George Appleby', ''),\n",
       " ('CH_139', 'King Stannis Baratheon Dwarf', 'Maxwell Laird', ''),\n",
       " ('CH_140', \"King's Landing Rioter #1\", 'Reg Wayment', ''),\n",
       " ('CH_141', \"King's Landing Rioter #2\", 'Marko Juraga', ''),\n",
       " ('CH_142', \"King's Landing Rioter #3\", 'Rea Separovic', ''),\n",
       " ('CH_143', 'Kinvara', 'Ania Bukstein', ''),\n",
       " ('CH_144', 'Kitty Frey', 'Lucy Hayes', ''),\n",
       " ('CH_145', 'Knight of House Frey', 'Patrick Ryan', ''),\n",
       " ('CH_146', 'Knight of House Lynderly', 'Paddy Rocks', ''),\n",
       " ('CH_147', 'Kovarro', 'Steven Cole', ''),\n",
       " ('CH_148', 'Kraznys mo Nakloz', 'Dan Hildebrand', ''),\n",
       " ('CH_149', 'Kurleket', 'Kevin Keenan', ''),\n",
       " ('CH_151', 'Lady Crane', 'Essie Davis', ''),\n",
       " ('CH_152', 'Lancel Lannister', 'Eugene Simon', ''),\n",
       " ('CH_153', 'Lannister Captain', 'Aiden Condron', ''),\n",
       " ('CH_154', 'Lannister Captain', 'Richard Laing', ''),\n",
       " ('CH_155', 'Lannister Guardsman', 'Conor Delaney', ''),\n",
       " ('CH_156', 'Lannister Messenger', 'Simon Stewart', ''),\n",
       " ('CH_157', 'Lannister Scout', 'Chris Gallagher', ''),\n",
       " ('CH_158', 'Lead Dornish Guard', 'Christian Vit', ''),\n",
       " ('CH_159', 'Leaf', 'Kae Alexander', '6.0'),\n",
       " ('CH_159', 'Leaf', 'Octavia Selena Alexandru', '4.0'),\n",
       " ('CH_160', 'Lem Lemoncloak', 'Jóhannes Haukur Jóhannesson', ''),\n",
       " ('CH_161', 'Leo Lefford', 'Vinnie McCabe', ''),\n",
       " ('CH_162', 'Lhara', 'Sarine Sofair', ''),\n",
       " ('CH_163', 'Little Bird', 'Samuel Paul Small', ''),\n",
       " ('CH_164', 'Little Bird', 'Tristan Mercieca', ''),\n",
       " ('CH_165', 'Little Bird #3', 'Michael Nevin', ''),\n",
       " ('CH_166', 'Little Bird #4', 'Jesse Magee', ''),\n",
       " ('CH_167', 'Little Bird #5', 'Iona Clarke', ''),\n",
       " ('CH_168', 'Little Bird #6', 'Lucy Gallagher', ''),\n",
       " ('CH_169', 'Little Bird #7', 'Fionn Kernan', ''),\n",
       " ('CH_170', 'Loboda', 'Zachary Baharov', ''),\n",
       " ('CH_171', 'Locke', 'Noah Taylor', ''),\n",
       " ('CH_172', 'Lollys Stokeworth', 'Elizabeth Cadwallader', ''),\n",
       " ('CH_173', 'Lommy Greenhands', 'Eros Vlahos', ''),\n",
       " ('CH_174', 'Loras Tyrell', 'Finn Jones', ''),\n",
       " ('CH_175', 'Lord Galbart Glover', 'Mark Coney', ''),\n",
       " ('CH_176', 'Lord Portan', 'Gordon Fulton', ''),\n",
       " ('CH_177', 'Lord Varys', 'Conleth Hill', ''),\n",
       " ('CH_178', 'Lordsport Dockhand', \"Jer O'Leary\", ''),\n",
       " ('CH_179', 'Lothar Frey', 'Daniel Tuite', '6.0'),\n",
       " ('CH_179', 'Lothar Frey', 'Tom Brooke', '3.0'),\n",
       " ('CH_180', \"Loyal Night's Watchman #1\", 'Steve Cash', ''),\n",
       " ('CH_181', \"Loyal Night's Watchman #2\", 'Robert Fawsitt', ''),\n",
       " ('CH_182', 'Lyanna Mormont', 'Bella Ramsey', ''),\n",
       " ('CH_183', 'Lyanna Stark', 'Aisling Franciosi', ''),\n",
       " ('CH_184', 'Lysa Arryn', 'Kate Dickie', ''),\n",
       " ('CH_185', 'Mace Tyrell', 'Roger Ashton-Griffiths', ''),\n",
       " ('CH_186', 'Maester Aemon', 'Peter Vaughan', ''),\n",
       " ('CH_187', 'Maester Cressen', 'Oliver Ford Davies', ''),\n",
       " ('CH_188', 'Maester Luwin', 'Donald Sumpter', ''),\n",
       " ('CH_189', 'Maester Wolkan', 'Richard Rycroft', ''),\n",
       " ('CH_190', 'Mag the Mighty', 'Neil Fingleton', ''),\n",
       " ('CH_191', 'Maggy', 'Jodhi May', ''),\n",
       " ('CH_192', 'Mago', 'Ivailo Dimitrov', ''),\n",
       " ('CH_193', 'Male Prostitute', 'Dennis Stokes', ''),\n",
       " ('CH_194', 'Malko', 'Adewale Akinnuoye-Agbaje', ''),\n",
       " ('CH_195', 'Mance Rayder', 'Ciarán Hinds', ''),\n",
       " ('CH_196', 'Mandon Moore', 'James Doran', ''),\n",
       " ('CH_197', 'Manservant', 'Conor Watters', ''),\n",
       " ('CH_198', 'Marei', 'Josephine Gillan', ''),\n",
       " ('CH_199', 'Margaery Tyrell', 'Natalie Dormer', ''),\n",
       " ('CH_200', \"Margaery's Handmaiden\", 'Aisling Jarrett-Gavin', ''),\n",
       " ('CH_201', 'Marillion', 'Emun Elliott', ''),\n",
       " ('CH_202', 'Martyn Lannister', 'Dean-Charles Chapman', ''),\n",
       " ('CH_203', 'Masha Heddle', 'Susie Kelly', ''),\n",
       " ('CH_204', 'Matthos Seaworth', 'Kerr Logan', ''),\n",
       " ('CH_205', 'Meera Reed', 'Ellie Kendrick', ''),\n",
       " ('CH_206', 'Meereen Slave Master', 'Laurence Doherty', ''),\n",
       " ('CH_207', 'Melessa Tarly', 'Samantha Spiro', ''),\n",
       " ('CH_208', 'Melisandre', 'Carice van Houten', ''),\n",
       " ('CH_209', 'Merchant Captain', 'Gary Pillai', ''),\n",
       " ('CH_210', 'Mero', 'Mark Killeen', ''),\n",
       " ('CH_211', 'Meryn Trant', 'Ian Beattie', ''),\n",
       " ('CH_212', 'Mhaegen', 'Antonia Christophers', ''),\n",
       " ('CH_213', 'Mikken', 'Boyd Rankin', ''),\n",
       " ('CH_214', 'Mirelle', 'Elisa Lasowski', ''),\n",
       " ('CH_215', 'Mirri Maz Duur', 'Mia Soteriou', ''),\n",
       " ('CH_216', 'Missandei', 'Nathalie Emmanuel', ''),\n",
       " ('CH_217', 'Mord', 'Ciaran Bermingham', ''),\n",
       " ('CH_218', 'Mossador', 'Reece Noi', ''),\n",
       " ('CH_219', 'Musician #1', 'Nanna Bryndís Hilmarsdóttir', ''),\n",
       " ('CH_220', 'Musician #1', 'Jon Thor Birgisson', ''),\n",
       " ('CH_221', 'Musician #2', 'Ragnar Þórhallsson', ''),\n",
       " ('CH_222', 'Musician #2', 'Orri P. Dyrason', ''),\n",
       " ('CH_223', 'Musician #3', 'Georg Holm', ''),\n",
       " ('CH_224', 'Musician #3', 'Arnar Rosenkranz Hilmarsson', ''),\n",
       " ('CH_225', 'Musician #4', 'Brynjar Leifsson', ''),\n",
       " ('CH_226', 'Musician #5', 'Kristján Kristjánsson', ''),\n",
       " ('CH_227', 'Mycah', 'Rhodri Hosking', ''),\n",
       " ('CH_228', 'Myranda', 'Charlotte Hope', ''),\n",
       " ('CH_229', 'Myrcella Baratheon', 'Aimee Richardson', '1.0'),\n",
       " ('CH_229', 'Myrcella Baratheon', 'Aimee Richardson', '2.0'),\n",
       " ('CH_229', 'Myrcella Baratheon', 'Nell Tiger Free', '5.0'),\n",
       " ('CH_229', 'Myrcella Baratheon', 'Nell Tiger Free', '6.0'),\n",
       " ('CH_230', 'Ned Umber', 'Harry Grasby', ''),\n",
       " ('CH_231', \"Night's Watch Deserter\", \"Barry John O'Connor\", ''),\n",
       " ('CH_232', \"Night's Watch Messenger\", \"Frank O'Sullivan\", ''),\n",
       " ('CH_233', \"Night's Watch Officer\", 'Dennis McKeever', ''),\n",
       " ('CH_234', \"Night's Watch Officer\", 'Michael Stuart', ''),\n",
       " ('CH_235', \"Night's Watchman\", 'Marcus Lamb', ''),\n",
       " ('CH_236', \"Night's Watchman\", 'Michael Hooley', ''),\n",
       " ('CH_237', \"Night's Watchman\", 'Patrick J Molloy', ''),\n",
       " ('CH_238', \"Night's Watchman #1\", 'Michael Hayes', ''),\n",
       " ('CH_239', \"Night's Watchman #2\", 'Ruairí Heading', ''),\n",
       " ('CH_240', \"Night's Watchman #2\", 'Tony Flynn', ''),\n",
       " ('CH_241', 'Northman Rioter', 'Jed Murray', ''),\n",
       " ('CH_243', 'Nymeria Sand', 'Jessica Henwick', ''),\n",
       " ('CH_244', 'Obara Sand', 'Keisha Castle-Hughes', ''),\n",
       " ('CH_245', 'Oberyn Martell', 'Pedro Pascal', ''),\n",
       " ('CH_246', 'Old Nan', 'Margaret John', ''),\n",
       " ('CH_247', 'Old Woman Prisoner', 'Gina Moxley', ''),\n",
       " ('CH_248', 'Olenna Tyrell', 'Diana Rigg', ''),\n",
       " ('CH_249', 'Olly', \"Brenock O'Connor\", ''),\n",
       " ('CH_250', 'Olyvar', 'Will Tudor', ''),\n",
       " ('CH_251', 'Orell', 'Mackenzie Crook', ''),\n",
       " ('CH_252', 'Ornela', 'Hannah John-Kamen', ''),\n",
       " ('CH_253', 'Osha', 'Natalia Tena', ''),\n",
       " ('CH_254', 'Othell Yarwyck', 'Brian Fortune', ''),\n",
       " ('CH_256', 'Pentoshi Servant', 'Rania Zouari', ''),\n",
       " ('CH_257', 'Petyr Baelish', 'Aidan Gillen', ''),\n",
       " ('CH_258', 'Podrick Payne', 'Daniel Portman', ''),\n",
       " ('CH_259', 'Polliver', 'Andy Kellegher', ''),\n",
       " ('CH_260', 'Prendahl na Ghezn', 'Ramon Tikaram', ''),\n",
       " ('CH_261', 'Pyat Pree', 'Ian Hanmore', ''),\n",
       " ('CH_262', 'Pypar', 'Josef Altin', ''),\n",
       " ('CH_263', 'Qhono', 'Staz Nair', ''),\n",
       " ('CH_264', 'Qhorin Halfhand', 'Simon Armstrong', ''),\n",
       " ('CH_265', 'Qotho', 'Dar Salim', ''),\n",
       " ('CH_266', 'Quaithe', 'Laura Pradelska', ''),\n",
       " ('CH_267', 'Qyburn', 'Anton Lesser', ''),\n",
       " ('CH_268', 'Rakharo', 'Elyes Gabel', ''),\n",
       " ('CH_269', 'Ramsay Snow', 'Iwan Rheon', ''),\n",
       " ('CH_270', 'Randyll Tarly', 'James Faulkner', ''),\n",
       " ('CH_271', 'Rast', 'Luke Barnes', ''),\n",
       " ('CH_272', 'Rattleshirt', 'Edward Dogliani', '2.0'),\n",
       " ('CH_272', 'Rattleshirt', 'Edward Dogliani', '3.0'),\n",
       " ('CH_272', 'Rattleshirt', \"Ross O'Hennessy\", '5.0'),\n",
       " ('CH_273', 'Razdal mo Eraz', 'George Georgiou', ''),\n",
       " ('CH_274', 'Red Keep Stableboy', 'Hugo Culverhouse', ''),\n",
       " ('CH_275', 'Red Priestess', 'Melanie Liburd', ''),\n",
       " ('CH_276', 'Red Priestess', 'Rila Fukushima', ''),\n",
       " ('CH_277', 'Renly Baratheon', 'Gethin Anthony', ''),\n",
       " ('CH_283', 'Rickard Karstark', 'John Stahl', '2.0'),\n",
       " ('CH_283', 'Rickard Karstark', 'John Stahl', '3.0'),\n",
       " ('CH_283', 'Rickard Karstark', 'Steve Blount', '1.0'),\n",
       " ('CH_285', 'Rickon Stark', 'Art Parkinson', ''),\n",
       " ('CH_286', 'Robb Stark', 'Richard Madden', ''),\n",
       " ('CH_287', 'Robert Baratheon', 'Mark Addy', ''),\n",
       " ('CH_288', 'Robett Glover', 'Tim McInnerny', ''),\n",
       " ('CH_289', 'Robin Arryn', 'Lino Facioli', ''),\n",
       " ('CH_290', 'Rodrik Cassel', 'Ron Donachie', ''),\n",
       " ('CH_291', 'Roose Bolton', 'Michael McElhatton', ''),\n",
       " ('CH_292', 'Rorge', 'Andy Beckwith', ''),\n",
       " ('CH_293', 'Ros', 'Esmé Bianco', ''),\n",
       " ('CH_294', 'Roslin Frey', 'Alexandra Dowling', ''),\n",
       " ('CH_295', 'Royal Steward', 'Robert Sterne', ''),\n",
       " ('CH_296', 'Ryger Rivers', 'Bryan McCaugherty', ''),\n",
       " ('CH_297', 'Salladhor Saan', 'Lucian Msamati', ''),\n",
       " ('CH_298', 'Samwell Tarly', 'John Bradley', ''),\n",
       " ('CH_299', 'Sandor Clegane', 'Rory McCann', ''),\n",
       " ('CH_300', 'Sansa Stark', 'Sophie Turner', ''),\n",
       " ('CH_301', 'Selyse Baratheon', 'Tara Fitzgerald', '3.0'),\n",
       " ('CH_301', 'Selyse Baratheon', 'Tara Fitzgerald', '4.0'),\n",
       " ('CH_301', 'Selyse Baratheon', 'Tara Fitzgerald', '5.0'),\n",
       " ('CH_301', 'Selyse Baratheon', 'Sarah MacKeever', '2.0'),\n",
       " ('CH_302', 'Septa Moelle', 'Maggie Hayes', ''),\n",
       " ('CH_303', 'Septa Mordane', 'Susan Brown', ''),\n",
       " ('CH_304', 'Septa Scolera', 'Mary Jordan', ''),\n",
       " ('CH_305', 'Septa Unella', 'Hannah Waddingham', ''),\n",
       " ('CH_306', 'Septon', 'Kenneth Hadley', ''),\n",
       " ('CH_307', 'Septon', 'Terry Byrne', ''),\n",
       " ('CH_308', 'Ser Endrew Tarth', 'Dez McMahon', ''),\n",
       " ('CH_309', 'Shae', 'Sibel Kekilli', ''),\n",
       " ('CH_310', 'Shagga', 'Mark Lewis Jones', ''),\n",
       " ('CH_312', 'Shireen Baratheon', 'Kerry Ingram', ''),\n",
       " ('CH_313', 'Silk King', 'Slavko Juraga', ''),\n",
       " ('CH_314', 'Smalljon Umber', 'Dean S. Jagger', ''),\n",
       " ('CH_315', 'Spice King', 'Nicholas Blane', ''),\n",
       " ('CH_316', 'Stannis Baratheon', 'Stephen Dillane', ''),\n",
       " ('CH_317', 'Stark Bannerman', 'Michael Stuart', ''),\n",
       " ('CH_318', 'Stark Guard', 'Faolan Morgan', ''),\n",
       " ('CH_319', 'Stark Guard', 'Rick Burn', ''),\n",
       " ('CH_320', 'Stark Guard', 'David Sheehan', ''),\n",
       " ('CH_321', 'Stark Guard', 'Paul Monahan', ''),\n",
       " ('CH_322', 'Steelshanks Walton', 'Jamie Michie', ''),\n",
       " ('CH_323', 'Stevron Frey', 'Colin Carnegie', ''),\n",
       " ('CH_324', 'Stiv', 'Stephen Don', ''),\n",
       " ('CH_325', 'Styr', 'Yuri Kolokolnikov', ''),\n",
       " ('CH_327', 'Syrio Forel', 'Miltos Yerolemou', ''),\n",
       " ('CH_328', 'Talisa Maegyr', 'Oona Chaplin', ''),\n",
       " ('CH_329', 'Talla Tarly', 'Rebecca Benson', ''),\n",
       " ('CH_330', 'Ternesio Terys', 'Gary Oliver', ''),\n",
       " ('CH_331', 'The Bear', 'Bart the Bear', ''),\n",
       " ('CH_332', 'The Night King', 'Richard Brake', '4.0'),\n",
       " ('CH_332', 'The Night King', 'Richard Brake', '5.0'),\n",
       " ('CH_332', 'The Night King', \"Vladimir 'Furdo' Furdik\", '6.0'),\n",
       " ('CH_332', 'The Night King', \"Vladimir 'Furdo' Furdik\", '7.0'),\n",
       " ('CH_332', 'The Night King', \"Vladimir 'Furdo' Furdik\", '8.0'),\n",
       " ('CH_333', 'The Tickler', 'Anthony Morris', ''),\n",
       " ('CH_334', 'The Waif', 'Faye Marsay', ''),\n",
       " ('CH_335', 'Thenn Warg', 'Joseph Gatt', ''),\n",
       " ('CH_336', 'Theon Greyjoy', 'Alfie Allen', ''),\n",
       " ('CH_337', 'Thin Man', 'Oengus MacNamara', ''),\n",
       " ('CH_338', 'Thoros of Myr', 'Paul Kaye', ''),\n",
       " ('CH_339', 'Three-Eyed Raven', 'Struan Rodger', '4.0'),\n",
       " ('CH_339', 'Three-Eyed Raven', 'Max von Sydow', '6.0'),\n",
       " ('CH_340', 'Timett', 'Tobias Winter', ''),\n",
       " ('CH_341', 'Tobho Mott', 'Andrew Wilde', ''),\n",
       " ('CH_342', 'Todder', \"Will O'Connell\", ''),\n",
       " ('CH_343', 'Tomard', 'Phil Dixon', ''),\n",
       " ('CH_344', 'Tommen Baratheon', 'Callum Wharry', '1.0'),\n",
       " ('CH_344', 'Tommen Baratheon', 'Callum Wharry', '2.0'),\n",
       " ('CH_344', 'Tommen Baratheon', 'Dean-Charles Chapman', '4.0'),\n",
       " ('CH_344', 'Tommen Baratheon', 'Dean-Charles Chapman', '5.0'),\n",
       " ('CH_344', 'Tommen Baratheon', 'Dean-Charles Chapman', '6.0'),\n",
       " ('CH_345', 'Tormund Giantsbane', 'Kristofer Hivju', ''),\n",
       " ('CH_346', 'Trystane Martell', 'Toby Sebastian', ''),\n",
       " ('CH_347', 'Tycho Nestoris', 'Mark Gatiss', ''),\n",
       " ('CH_348', 'Tyene Sand', 'Rosabell Laurenti Sellers', ''),\n",
       " ('CH_349', 'Tyrion Lannister', 'Peter Dinklage', ''),\n",
       " ('CH_350', 'Tywin Lannister', 'Charles Dance', ''),\n",
       " ('CH_351', 'Vala', 'Meena Rayann', ''),\n",
       " ('CH_352', 'Vardis Egen', 'Brendan McCormack', ''),\n",
       " ('CH_353', 'Varly', 'Graham Charles', ''),\n",
       " ('CH_355', 'Viserys Targaryen', 'Harry Lloyd', ''),\n",
       " ('CH_356', 'Walda Bolton', 'Elizabeth Webster', ''),\n",
       " ('CH_357', 'Walder Frey', 'David Bradley', ''),\n",
       " ('CH_358', 'Wallen', \"Barry O'Connor\", ''),\n",
       " ('CH_359', 'Warlock', 'Lottie Steer', ''),\n",
       " ('CH_360', 'Waymar Royce', 'Rob Ostlere', ''),\n",
       " ('CH_361', 'Wedding Band', 'Sigur Rós', ''),\n",
       " ('CH_362', 'White Walker', 'Ian Whyte', ''),\n",
       " ('CH_363', 'White Walker', 'Ross Mullan', ''),\n",
       " ('CH_364', 'White Walker', 'Tim Loane', ''),\n",
       " ('CH_365', 'White Walker #2', 'Spencer Wilding', ''),\n",
       " ('CH_366', 'Wight Wildling Girl', 'Claire Wright', ''),\n",
       " ('CH_367', 'Wildling Rioter', 'Michael Patrick', ''),\n",
       " ('CH_368', 'Will', 'Bronson Webb', ''),\n",
       " ('CH_369', 'Willem Lannister', 'Timothy Gibbons', ''),\n",
       " ('CH_370', 'Willis Wode', 'Ryan McKenna', ''),\n",
       " ('CH_371', 'Wine Merchant', 'Simon Lowe', ''),\n",
       " ('CH_372', 'Winterfell Shepherd', 'Donagh Deeney', ''),\n",
       " ('CH_373', 'Wun Wun', 'Ian Whyte', ''),\n",
       " ('CH_374', 'Wyllis', 'Sam Coleman', ''),\n",
       " ('CH_375', 'Xaro Xhoan Daxos', 'Nonso Anozie', ''),\n",
       " ('CH_376', 'Yara Greyjoy', 'Gemma Whelan', ''),\n",
       " ('CH_377', 'Yezzan zo Qaggaz', 'Enzo Cilenti', ''),\n",
       " ('CH_378', 'Ygritte', 'Rose Leslie', ''),\n",
       " ('CH_379', 'Yohn Royce', 'Rupert Vansittart', ''),\n",
       " ('CH_380', 'Yoren', 'Francis Magee', ''),\n",
       " ('CH_381', 'Young Benjen Stark', 'Matteo Elezi', ''),\n",
       " ('CH_382', 'Young Cersei Lannister', 'Nell Williams', ''),\n",
       " ('CH_383', 'Young Lyanna Stark', 'Cordelia Hill', ''),\n",
       " ('CH_384', 'Young Nan', 'Annette Tierney', ''),\n",
       " ('CH_385', 'Young Ned', 'Robert Aramayo', ''),\n",
       " ('CH_386', 'Young Ned Stark', 'Sebastian Croft', ''),\n",
       " ('CH_387', 'Young Rodrik Cassel', 'Fergus Leathem', ''),\n",
       " ('CH_388', 'Zanrush', 'Gerald Lepkowski', '')]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT\n",
    "    character_id,\n",
    "    (select characterName from characters where characters.character_id=\n",
    "         characters_actors.character_id) as characterName,\n",
    "    actorName, season\n",
    "FROM W4111GoTSolution.characters_actors;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data Cleanup\n",
    "\n",
    "### Task 2\n",
    "\n",
    "- CSV files are _text_ files, and all data is character strings.\n",
    "\n",
    "\n",
    "- Spreadsheet applications, like Excel, and the data import tools in databases can _infer_ column types in some cases. For example, consider the table definition created for importing ```scenes_characters.```\n",
    "\n",
    "```\n",
    "CREATE TABLE `scenes_characters` (\n",
    "  `id` int(11) DEFAULT NULL,\n",
    "  `seasonNum` int(11) DEFAULT NULL,\n",
    "  `episodeNum` int(11) DEFAULT NULL,\n",
    "  `sceneNo` int(11) DEFAULT NULL,\n",
    "  `characterName` text\n",
    ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;\n",
    "\n",
    "```\n",
    "\n",
    "\n",
    "- The import process inferred that some columns in the CSV file are text representations of integers, and performed conversion.\n",
    "\n",
    "\n",
    "- The process is unreliable, and does not always work.\n",
    "\n",
    "\n",
    "- For this task, you must modify column definitions and data valaues to represent the correct the meaning of the data. We will cover keys in the next task.\n",
    "\n",
    "\n",
    "- Execute your DDL and DML SQL in cells below. You can add Markdown cells to explain what you are doing.\n",
    "\n",
    "\n",
    "- My approach is to create a new schema with corrected table definitions and cleaned copies of the data. This preserves the original data for data lineage and avoids possible information loss.\n",
    "\n",
    "\n",
    "- Call your new schema ```W4111GoTSolutionClean.```\n",
    "\n",
    "\n",
    "- An example using a table called groups is given below\n",
    "\n",
    "\n",
    "- __NOTE:__ You can use MySQL Workbench or other tools to help you write the DDL and DML statements, but you MUST execute the statements in the cells below."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Create the Schema:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@localhost/W4111GoTSolution\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql CREATE SCHEMA `W4111GoTSolutionClean`;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The sql extension is already loaded. To reload it, use:\n",
      "  %reload_ext sql\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "'Connected: root@W4111GoTSolutionclean'"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%load_ext sql\n",
    "%sql mysql+pymysql://root:ttl980305@localhost/W4111GoTSolutionclean"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Example: Groups\n",
    "\n",
    "- You should have similar queries for the tables that you are cleaning up. \n",
    "\n",
    "- Create a table with clean data\n",
    "\n",
    "%%sql\n",
    "create table W4111GotSolutionClean.groups (id int, groupName varchar(32), characterName varchar(32))\n",
    "\n",
    "- Insert the data into the new table from the original\n",
    "\n",
    "%%sql insert into W4111GotSolutionClean.groups\n",
    "select id, groupName, `character` from W4111GotSolution.groups;\n",
    "        \n",
    "- Check to make sure the data imported successfully\n",
    "\n",
    "%sql select count(*) from W4111GotSolution.groups\n",
    "\n",
    " - ```''``` is a common way to represent \"unknown\" or \"not applicable\" in text data.[Codd's Rule no. 3](https://www.webopedia.com/TERM/C/Codds_Rules.html) defines the systmatic treatment of unknown or  not applicable data. So we should update this accordingly. \n",
    "\n",
    "%%sql update W4111GoTSolutionClean.groups set characterName=NULL where characterName=''"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Answers"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "__characters__"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-Create table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@localhost/W4111GoTSolution\n",
      "0 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql drop table if exists characters;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@localhost/W4111GoTSolution\n",
      "389 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql create table w4111gotsolutionclean.characters\n",
    "  as select * from w4111gotsolution.characters;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-Setting types on columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@localhost/W4111GoTSolution\n",
      "389 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql ALTER TABLE `w4111gotsolutionclean`.`characters` \n",
    "CHANGE COLUMN `characterName` `characterName` VARCHAR(64) NULL DEFAULT NULL ,\n",
    "CHANGE COLUMN `characterLink` `characterLink` VARCHAR(1024) NULL DEFAULT NULL ,\n",
    "CHANGE COLUMN `actorName` `actorName` VARCHAR(64) NULL DEFAULT NULL ,\n",
    "CHANGE COLUMN `actorLink` `actorLink` VARCHAR(1024) NULL DEFAULT NULL ,\n",
    "CHANGE COLUMN `character_id` `character_id` VARCHAR(12) NULL DEFAULT NULL ,\n",
    "CHANGE COLUMN `characterImageThumb` `characterImageThumb` VARCHAR(1024) NULL DEFAULT NULL ,\n",
    "CHANGE COLUMN `characterImageFull` `characterImageFull` VARCHAR(1024) NULL DEFAULT NULL ,\n",
    "CHANGE COLUMN `nickname` `nickname` VARCHAR(64) NULL DEFAULT NULL ;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-Dealing with Null"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@localhost/W4111GoTSolution\n",
      "0 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql ALTER TABLE `w4111gotsolutionclean`.`characters` \n",
    "CHANGE COLUMN `id` `id` INT NOT NULL ,\n",
    "CHANGE COLUMN `characterName` `characterName` VARCHAR(64) NOT NULL ,\n",
    "CHANGE COLUMN `character_id` `character_id` VARCHAR(12) NOT NULL ,\n",
    "ADD PRIMARY KEY (`id`),\n",
    "ADD UNIQUE INDEX `character_id_UNIQUE` (`character_id` ASC) VISIBLE;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-Replace \"\" with NULL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@localhost/W4111GoTSolution\n",
      "35 rows affected.\n",
      "21 rows affected.\n",
      "0 rows affected.\n",
      "36 rows affected.\n",
      "134 rows affected.\n",
      "134 rows affected.\n",
      "380 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql update characters set actorName=NULL where actorName=\"\";\n",
    "update characters set characterLink=NULL where characterLink=\"\";\n",
    "update characters set actorName=NULL where actorName=\"\";\n",
    "update characters set actorLink=NULL where actorLink=\"\";\n",
    "update characters set characterImageFull=NULL where characterImageFull=\"\";\n",
    "update characters set characterImageThumb=NULL where characterImageThumb=\"\";\n",
    "update characters set nickname=NULL where nickname=\"\";"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-Boolean"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@localhost/W4111GoTSolution\n",
      "17 rows affected.\n",
      "372 rows affected.\n",
      "5 rows affected.\n",
      "384 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql update characters set royal=1 where royal=\"True\";\n",
    "update characters set royal=0 where royal=\"\";\n",
    "update characters set kingsguard=1 where kingsguard=\"True\";\n",
    "update characters set kingsguard=0 where kingsguard=\"\";"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@localhost/W4111GoTSolution\n",
      "389 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql ALTER TABLE `w4111gotsolutionclean`.`characters` \n",
    "CHANGE COLUMN `royal` `royal` TINYINT NOT NULL DEFAULT 0 ,\n",
    "CHANGE COLUMN `kingsguard` `kingsguard` TINYINT NOT NULL DEFAULT 0 ;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@localhost/W4111GoTSolution\n",
      "11 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>Field</th>\n",
       "        <th>Type</th>\n",
       "        <th>Null</th>\n",
       "        <th>Key</th>\n",
       "        <th>Default</th>\n",
       "        <th>Extra</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>id</td>\n",
       "        <td>int</td>\n",
       "        <td>NO</td>\n",
       "        <td>PRI</td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>characterName</td>\n",
       "        <td>varchar(64)</td>\n",
       "        <td>NO</td>\n",
       "        <td></td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>characterLink</td>\n",
       "        <td>varchar(1024)</td>\n",
       "        <td>YES</td>\n",
       "        <td></td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>actorName</td>\n",
       "        <td>varchar(64)</td>\n",
       "        <td>YES</td>\n",
       "        <td></td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>actorLink</td>\n",
       "        <td>varchar(1024)</td>\n",
       "        <td>YES</td>\n",
       "        <td></td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>character_id</td>\n",
       "        <td>varchar(12)</td>\n",
       "        <td>NO</td>\n",
       "        <td>UNI</td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>royal</td>\n",
       "        <td>tinyint</td>\n",
       "        <td>NO</td>\n",
       "        <td></td>\n",
       "        <td>0</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>characterImageThumb</td>\n",
       "        <td>varchar(1024)</td>\n",
       "        <td>YES</td>\n",
       "        <td></td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>characterImageFull</td>\n",
       "        <td>varchar(1024)</td>\n",
       "        <td>YES</td>\n",
       "        <td></td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>nickname</td>\n",
       "        <td>varchar(64)</td>\n",
       "        <td>YES</td>\n",
       "        <td></td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>kingsguard</td>\n",
       "        <td>tinyint</td>\n",
       "        <td>NO</td>\n",
       "        <td></td>\n",
       "        <td>0</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('id', 'int', 'NO', 'PRI', None, ''),\n",
       " ('characterName', 'varchar(64)', 'NO', '', None, ''),\n",
       " ('characterLink', 'varchar(1024)', 'YES', '', None, ''),\n",
       " ('actorName', 'varchar(64)', 'YES', '', None, ''),\n",
       " ('actorLink', 'varchar(1024)', 'YES', '', None, ''),\n",
       " ('character_id', 'varchar(12)', 'NO', 'UNI', None, ''),\n",
       " ('royal', 'tinyint', 'NO', '', '0', ''),\n",
       " ('characterImageThumb', 'varchar(1024)', 'YES', '', None, ''),\n",
       " ('characterImageFull', 'varchar(1024)', 'YES', '', None, ''),\n",
       " ('nickname', 'varchar(64)', 'YES', '', None, ''),\n",
       " ('kingsguard', 'tinyint', 'NO', '', '0', '')]"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql describe w4111gotsolutionclean.characters"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "__scenes__"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-create tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@localhost/W4111GoTSolution\n",
      "0 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql drop table if exists w4111gotsolutionclean.scenes;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@localhost/W4111GoTSolution\n",
      "4165 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql create table w4111gotsolutionclean.scenes\n",
    "  as select * from w4111gotsolution.scenes;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-Setting types"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@localhost/W4111GoTSolution\n",
      "4165 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql ALTER TABLE `w4111gotsolutionclean`.`scenes` \n",
    "CHANGE COLUMN `id` `id` INT NULL ,\n",
    "CHANGE COLUMN `seasonNum` `seasonNum` INT(11) NULL ,\n",
    "CHANGE COLUMN `episodeNum` `episodeNum` INT(11) NULL ,\n",
    "CHANGE COLUMN `sceneNo` `sceneNo` INT(11) NULL ,\n",
    "CHANGE COLUMN `sceneStart` `sceneStart` TIME NULL ,\n",
    "CHANGE COLUMN `sceneEnd` `sceneEnd` TIME NULL ,\n",
    "CHANGE COLUMN `location` `location` VARCHAR(64) NULL DEFAULT NULL ,\n",
    "CHANGE COLUMN `subLocation` `subLocation` VARCHAR(64) NULL DEFAULT NULL ,\n",
    "CHANGE COLUMN `altLocation` `altLocation` VARCHAR(64) NULL DEFAULT NULL ;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@localhost/W4111GoTSolution\n",
      "0 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql ALTER TABLE `w4111gotsolutionclean`.`scenes` \n",
    "DROP COLUMN `id`;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-Null constraints"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@localhost/W4111GoTSolution\n",
      "0 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql ALTER TABLE `w4111gotsolutionclean`.`scenes` \n",
    "CHANGE COLUMN `seasonNum` `seasonNum` INT NOT NULL ,\n",
    "CHANGE COLUMN `episodeNum` `episodeNum` INT NOT NULL ,\n",
    "CHANGE COLUMN `sceneNo` `sceneNo` INT NOT NULL ,\n",
    "CHANGE COLUMN `sceneStart` `sceneStart` TIME NOT NULL ,\n",
    "CHANGE COLUMN `sceneEnd` `sceneEnd` TIME NOT NULL ,\n",
    "CHANGE COLUMN `location` `location` VARCHAR(64) NOT NULL ,\n",
    "ADD PRIMARY KEY (`seasonNum`, `episodeNum`, `sceneNo`);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-Boolean type"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@localhost/W4111GoTSolution\n",
      "16 rows affected.\n",
      "7 rows affected.\n",
      "41 rows affected.\n",
      "4149 rows affected.\n",
      "4158 rows affected.\n",
      "4124 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql update scenes set greensight=1 where greensight=\"True\";\n",
    "update scenes set warg=1 where warg=\"True\";\n",
    "update scenes set flashback\t=1 where flashback=\"True\";\n",
    "update scenes set greensight=0 where greensight=\"\";\n",
    "update scenes set warg=0 where warg=\"\";\n",
    "update scenes set flashback\t=0 where flashback=\"\";"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@localhost/W4111GoTSolution\n",
      "4165 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql ALTER TABLE `w4111gotsolutionclean`.`scenes` \n",
    "CHANGE COLUMN `greensight` `greensight` TINYINT NOT NULL DEFAULT '0' ,\n",
    "CHANGE COLUMN `warg` `warg` TINYINT NOT NULL DEFAULT '0' ,\n",
    "CHANGE COLUMN `flashback` `flashback` TINYINT NOT NULL DEFAULT '0' ;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@localhost/W4111GoTSolution\n",
      "12 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>Field</th>\n",
       "        <th>Type</th>\n",
       "        <th>Null</th>\n",
       "        <th>Key</th>\n",
       "        <th>Default</th>\n",
       "        <th>Extra</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>id</td>\n",
       "        <td>int</td>\n",
       "        <td>YES</td>\n",
       "        <td></td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>seasonNum</td>\n",
       "        <td>int</td>\n",
       "        <td>YES</td>\n",
       "        <td></td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>episodeNum</td>\n",
       "        <td>int</td>\n",
       "        <td>YES</td>\n",
       "        <td></td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>sceneNo</td>\n",
       "        <td>int</td>\n",
       "        <td>YES</td>\n",
       "        <td></td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>sceneStart</td>\n",
       "        <td>text</td>\n",
       "        <td>YES</td>\n",
       "        <td></td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>sceneEnd</td>\n",
       "        <td>text</td>\n",
       "        <td>YES</td>\n",
       "        <td></td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>location</td>\n",
       "        <td>text</td>\n",
       "        <td>YES</td>\n",
       "        <td></td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>subLocation</td>\n",
       "        <td>text</td>\n",
       "        <td>YES</td>\n",
       "        <td></td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>greensight</td>\n",
       "        <td>text</td>\n",
       "        <td>YES</td>\n",
       "        <td></td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>altLocation</td>\n",
       "        <td>text</td>\n",
       "        <td>YES</td>\n",
       "        <td></td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>warg</td>\n",
       "        <td>text</td>\n",
       "        <td>YES</td>\n",
       "        <td></td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>flashback</td>\n",
       "        <td>text</td>\n",
       "        <td>YES</td>\n",
       "        <td></td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('id', 'int', 'YES', '', None, ''),\n",
       " ('seasonNum', 'int', 'YES', '', None, ''),\n",
       " ('episodeNum', 'int', 'YES', '', None, ''),\n",
       " ('sceneNo', 'int', 'YES', '', None, ''),\n",
       " ('sceneStart', 'text', 'YES', '', None, ''),\n",
       " ('sceneEnd', 'text', 'YES', '', None, ''),\n",
       " ('location', 'text', 'YES', '', None, ''),\n",
       " ('subLocation', 'text', 'YES', '', None, ''),\n",
       " ('greensight', 'text', 'YES', '', None, ''),\n",
       " ('altLocation', 'text', 'YES', '', None, ''),\n",
       " ('warg', 'text', 'YES', '', None, ''),\n",
       " ('flashback', 'text', 'YES', '', None, '')]"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql describe scenes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "__scenes_characters__"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-Create table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@localhost/W4111GoTSolution\n",
      "0 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql drop table if exists w4111gotsolutionclean.scenes_characters;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@localhost/W4111GoTSolution\n",
      "12114 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql create table w4111gotsolutionclean.scenes_characters\n",
    "  as select * from w4111gotsolution.scenes_characters;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-Setting types"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@localhost/W4111GoTSolution\n",
      "12114 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql ALTER TABLE `w4111gotsolutionclean`.`scenes_characters` \n",
    "CHANGE COLUMN `id` `id` INT(11) NOT NULL ,\n",
    "CHANGE COLUMN `seasonNum` `seasonNum` INT(11) NOT NULL ,\n",
    "CHANGE COLUMN `episodeNum` `episodeNum` INT(11) NOT NULL ,\n",
    "CHANGE COLUMN `sceneNo` `sceneNo` INT(11) NOT NULL ,\n",
    "CHANGE COLUMN `characterName` `characterName` VARCHAR(64) NULL DEFAULT NULL ,\n",
    "ADD PRIMARY KEY (`id`);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-Setting foreign keys"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@localhost/W4111GoTSolution\n",
      "0 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql ALTER TABLE `w4111gotsolutionclean`.`scenes` \n",
    "ADD INDEX `seasonNum` (`seasonNum` ASC) INVISIBLE,\n",
    "ADD INDEX `episodeNum` (`episodeNum` ASC) INVISIBLE,\n",
    "ADD INDEX `sceneNo` (`sceneNo` ASC) VISIBLE;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@localhost/W4111GoTSolution\n",
      "12114 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql ALTER TABLE `w4111gotsolutionclean`.`scenes_characters` \n",
    "ADD CONSTRAINT `seasonNum`\n",
    "  FOREIGN KEY (`seasonNum`)\n",
    "REFERENCES `w4111gotsolutionclean`.`scenes` (`seasonNum`)\n",
    "  ON DELETE NO ACTION\n",
    "ON UPDATE NO ACTION,\n",
    "ADD CONSTRAINT `episodeNum`\n",
    "  FOREIGN KEY (`episodeNum`)\n",
    "REFERENCES `w4111gotsolutionclean`.`scenes` (`episodeNum`)\n",
    "  ON DELETE NO ACTION\n",
    "ON UPDATE NO ACTION,\n",
    "ADD CONSTRAINT `sceneNo`\n",
    "  FOREIGN KEY (`sceneNo`)\n",
    "REFERENCES `w4111gotsolutionclean`.`scenes` (`sceneNo`)\n",
    "  ON DELETE NO ACTION\n",
    "ON UPDATE NO ACTION;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@localhost/W4111GoTSolution\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>Field</th>\n",
       "        <th>Type</th>\n",
       "        <th>Null</th>\n",
       "        <th>Key</th>\n",
       "        <th>Default</th>\n",
       "        <th>Extra</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>id</td>\n",
       "        <td>int</td>\n",
       "        <td>YES</td>\n",
       "        <td></td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>seasonNum</td>\n",
       "        <td>int</td>\n",
       "        <td>YES</td>\n",
       "        <td></td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>episodeNum</td>\n",
       "        <td>int</td>\n",
       "        <td>YES</td>\n",
       "        <td></td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>sceneNo</td>\n",
       "        <td>int</td>\n",
       "        <td>YES</td>\n",
       "        <td></td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>characterName</td>\n",
       "        <td>text</td>\n",
       "        <td>YES</td>\n",
       "        <td></td>\n",
       "        <td>None</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('id', 'int', 'YES', '', None, ''),\n",
       " ('seasonNum', 'int', 'YES', '', None, ''),\n",
       " ('episodeNum', 'int', 'YES', '', None, ''),\n",
       " ('sceneNo', 'int', 'YES', '', None, ''),\n",
       " ('characterName', 'text', 'YES', '', None, '')]"
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql describe scenes_characters;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Task 3\n",
    "\n",
    "### Keys and Data Integrity\n",
    "\n",
    "- Correct analysis and management of the data requires defining keys and integrity constraints.\n",
    "\n",
    "\n",
    "- Use DML statements to modify the schema to create the constraints you think best.\n",
    "\n",
    "\n",
    "- The following diagram is my definition, but this is an example and may not be correct. It is simply to help you get started.\n",
    "\n",
    "| <img src=\"./got_er_model.png\"> |\n",
    "| :---: |\n",
    "|__GoT ER Model__ |\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Characters, Scenes, Scenes and Characters"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-I have done all the not null, primary key, foreign key and unique constraints in the TASK 2. What I am going to do next is add missing characterName to **characters** table and removes duplicates `characterName`. So that I can set `character_id` as foreign key in **scenes_character** table."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "(i) create a table contains all the character name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   mysql+pymysql://root:***@localhost/W4111GoTSolution\n",
      " * mysql+pymysql://root:***@localhost/W4111GoTSolutionclean\n",
      "0 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 88,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql drop table if exists character_ids;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   mysql+pymysql://root:***@localhost/W4111GoTSolution\n",
      " * mysql+pymysql://root:***@localhost/W4111GoTSolutionclean\n",
      "0 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 89,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql CREATE TABLE `W4111GoTSolutionclean`.`character_ids` (\n",
    "  `auto_id` INT NOT NULL AUTO_INCREMENT,\n",
    "  `characterName` VARCHAR(128) NULL,\n",
    "  `real_id` INT NULL,\n",
    "  `character_id` VARCHAR(16) GENERATED ALWAYS AS (concat(\"CH_\", real_id)) STORED,\n",
    "  PRIMARY KEY (`auto_id`));"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   mysql+pymysql://root:***@localhost/W4111GoTSolution\n",
      " * mysql+pymysql://root:***@localhost/W4111GoTSolutionclean\n",
      "389 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql insert into W4111GoTSolutionclean.character_ids(characterName, real_id)\n",
    "\tselect characterName, id from W4111GoTSolutionclean.characters order by id asc;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   mysql+pymysql://root:***@localhost/W4111GoTSolution\n",
      " * mysql+pymysql://root:***@localhost/W4111GoTSolutionclean\n",
      "238 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql insert into character_ids(characterName)\n",
    "\tSELECT distinct characterName FROM scenes_characters\n",
    "\t\t\twhere\n",
    "\t\t\t\tcharacterName not in (select characterName from characters);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   mysql+pymysql://root:***@localhost/W4111GoTSolution\n",
      " * mysql+pymysql://root:***@localhost/W4111GoTSolutionclean\n",
      "238 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 93,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql update character_ids\n",
    "\tset real_id = auto_id-1 where real_id is NULL;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "(ii) I deleted all the duplicates character name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   mysql+pymysql://root:***@localhost/W4111GoTSolution\n",
      " * mysql+pymysql://root:***@localhost/W4111GoTSolutionclean\n",
      "627 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 97,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql create table character_ids_copy \n",
    "    as select * from character_ids;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   mysql+pymysql://root:***@localhost/W4111GoTSolution\n",
      " * mysql+pymysql://root:***@localhost/W4111GoTSolutionclean\n",
      "19 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 98,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql delete from character_ids\n",
    "where characterName in (select characterName from character_ids_copy group by characterName having count(characterName) > 1)\n",
    "and auto_id not in (select min(auto_id) from character_ids_copy group by characterName having count(characterName)>1);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now I have a table named **character_ids** that has corresponding `characterName` and `character_id` and these pairs are unique."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-Add `character_id` column in the **scenes_characters** table and set it as the foreign key."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   mysql+pymysql://root:***@localhost/W4111GoTSolution\n",
      " * mysql+pymysql://root:***@localhost/W4111GoTSolutionclean\n",
      "0 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 99,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql alter table scenes_characters add character_id varchar(16);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   mysql+pymysql://root:***@localhost/W4111GoTSolution\n",
      " * mysql+pymysql://root:***@localhost/W4111GoTSolutionclean\n",
      "12114 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 103,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql update scenes_characters sc, character_ids ci\n",
    "    set sc.character_id=ci.character_id\n",
    "    where sc.characterName=ci.characterName;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-I change generated into not-generated (drop concat(_utf8mb4'CH_',`real_id`))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql ALTER TABLE `w4111gotsolutionclean`.`character_ids` \n",
    "CHANGE COLUMN `character_id` `character_id` VARCHAR(16) NOT NULL ,\n",
    "ADD UNIQUE INDEX `character_id_UNIQUE` (`character_id` ASC) VISIBLE;\n",
    ";"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "%%sql ALTER TABLE `w4111gotsolutionclean`.`scenes_characters` \n",
    "ADD INDEX `character_id_idx` (`character_id` ASC) VISIBLE;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   mysql+pymysql://root:***@localhost/W4111GoTSolution\n",
      " * mysql+pymysql://root:***@localhost/W4111GoTSolutionclean\n",
      "12114 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 106,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql ALTER TABLE `w4111gotsolutionclean`.`scenes_characters` \n",
    "ADD CONSTRAINT `character_id`\n",
    "  FOREIGN KEY (`character_id`)\n",
    "REFERENCES `w4111gotsolutionclean`.`character_ids` (`character_id`)\n",
    "  ON DELETE NO ACTION\n",
    "ON UPDATE NO ACTION;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql describe character_ids;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql describe scenes;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql describe scenes_character;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-My Reverse Engineer"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<img src=\"./my_ER_model.png\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Task 4\n",
    "\n",
    "### Views"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Character Interactions\n",
    "\n",
    "- ```Characters``` interacted if they appeared in the same ```scene.```\n",
    "\n",
    "\n",
    "- Create the following view:\n",
    "    - ```characterName_1```\n",
    "    - ```character_id_1```\n",
    "    - ```characterName_2```\n",
    "    - ```character_id_2```\n",
    "    - ```scene_count,``` number of scenes containing both ```characters.```\n",
    "    - Order by ```scene_count``` descending.\n",
    "\n",
    "- Each pair should appear only once, that is the table should not contain two rows of the form:\n",
    "    - ```character_1, character_2, scene_count```\n",
    "    - ```character_2, character_1, scene_count```\n",
    "    \n",
    "    \n",
    "<u>Answer:</u>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@localhost/W4111GoTSolutionclean\n",
      "0 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql drop view if exists character_interaction;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@localhost/W4111GoTSolutionclean\n",
      "0 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql create view character_interaction as\n",
    "    select a.characterName as characterName_1, \n",
    "    b.characterName as characterName_2, \n",
    "    count(*) as count \n",
    "from\n",
    "    scenes_characters as a\n",
    "\tjoin\n",
    "\tscenes_characters as b\n",
    "using(seasonNum,episodeNum,sceneNo)\n",
    "    where a.characterName<b.characterName\n",
    "\tgroup by characterName_1, characterName_2\n",
    "\torder by count DESC;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql select * from character_interaction limit 20;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Interesting View\n",
    "\n",
    "- Create a view of the information that you think would be interesting to fans.\n",
    "\n",
    "\n",
    "- Explain what you view is producing.\n",
    "\n",
    "<u>Answer:</u>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-I would like to create a view showing the appearances of each character in descending order."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@localhost/W4111GoTSolutionclean\n",
      "0 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql drop view if exists character_appearances;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@localhost/W4111GoTSolutionclean\n",
      "0 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql create view character_appearances as\n",
    "    select charactername, count(characterName) as counts from scenes_characters\n",
    "    group by characterName order by counts desc;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@localhost/W4111GoTSolutionclean\n",
      "20 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>charactername</th>\n",
       "        <th>counts</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Jon Snow</td>\n",
       "        <td>632</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Tyrion Lannister</td>\n",
       "        <td>544</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Daenerys Targaryen</td>\n",
       "        <td>509</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Arya Stark</td>\n",
       "        <td>360</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Sansa Stark</td>\n",
       "        <td>349</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Cersei Lannister</td>\n",
       "        <td>330</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Jaime Lannister</td>\n",
       "        <td>330</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Jorah Mormont</td>\n",
       "        <td>257</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Davos Seaworth</td>\n",
       "        <td>256</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bran Stark</td>\n",
       "        <td>248</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Samwell Tarly</td>\n",
       "        <td>225</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Sandor Clegane</td>\n",
       "        <td>216</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Tormund Giantsbane</td>\n",
       "        <td>215</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Drogon</td>\n",
       "        <td>194</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Lord Varys</td>\n",
       "        <td>194</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Brienne of Tarth</td>\n",
       "        <td>185</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Missandei</td>\n",
       "        <td>184</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Theon Greyjoy</td>\n",
       "        <td>179</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Grey Worm</td>\n",
       "        <td>176</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Podrick Payne</td>\n",
       "        <td>145</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Jon Snow', 632),\n",
       " ('Tyrion Lannister', 544),\n",
       " ('Daenerys Targaryen', 509),\n",
       " ('Arya Stark', 360),\n",
       " ('Sansa Stark', 349),\n",
       " ('Cersei Lannister', 330),\n",
       " ('Jaime Lannister', 330),\n",
       " ('Jorah Mormont', 257),\n",
       " ('Davos Seaworth', 256),\n",
       " ('Bran Stark', 248),\n",
       " ('Samwell Tarly', 225),\n",
       " ('Sandor Clegane', 216),\n",
       " ('Tormund Giantsbane', 215),\n",
       " ('Drogon', 194),\n",
       " ('Lord Varys', 194),\n",
       " ('Brienne of Tarth', 185),\n",
       " ('Missandei', 184),\n",
       " ('Theon Greyjoy', 179),\n",
       " ('Grey Worm', 176),\n",
       " ('Podrick Payne', 145)]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select * from character_appearances limit 20;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Task 5\n",
    "\n",
    "### Programming"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Data Processing and Cleansing\n",
    "\n",
    "\n",
    "- The ```scene``` information in ```episodes.json``` complements and extend some of the information about character interactions and relationships. An example is:\n",
    "\n",
    "```\n",
    "            {\n",
    "               \"sceneStart\":\"0:50:24\",\n",
    "               \"sceneEnd\":\"0:55:54\",\n",
    "               \"location\":\"The Crownlands\",\n",
    "               \"subLocation\":\"King's Landing\",\n",
    "               \"characters\":[\n",
    "                  {\n",
    "                     \"name\":\"Arya Stark\",\n",
    "                     \"weapon\":[\n",
    "                        {\"action\":\"has\", \"name\":\"Needle\"}\n",
    "                     ]\n",
    "                  },\n",
    "                  {\n",
    "                     \"name\":\"Eddard Stark\",\n",
    "                     \"title\":\"Hand\",\n",
    "                     \"alive\":false,\n",
    "                     \"mannerOfDeath\":\"Decapitation\",\n",
    "                     \"killedBy\":[\n",
    "                        \"Ilyn Payne\"\n",
    "                     ]\n",
    "                  },\n",
    "                  {\"name\":\"Yoren\"},\n",
    "                  {\"name\":\"Sansa Stark\"},\n",
    "                  {\"name\":\"Petyr Baelish\"},\n",
    "                  {\"name\":\"Cersei Lannister\"},\n",
    "                  {\"name\":\"Joffrey Baratheon\", \"title\":\"King\"},\n",
    "                  {\"name\":\"Grand Maester Pycelle\"},\n",
    "                  {\"name\":\"Lord Varys\"},\n",
    "                  {\"name\":\"Sandor Clegane\"},\n",
    "                  {\n",
    "                     \"name\":\"Ilyn Payne\",\n",
    "                     \"weapon\":[\n",
    "                        {\"action\":\"has\", \"name\":\"Ice\"}\n",
    "                     ]\n",
    "                  }\n",
    "               ]\n",
    "            }\n",
    "         ]\n",
    "```\n",
    "\n",
    "\n",
    "- A little data processing determines that the top-level elements of a ```character``` in a scene are:\n",
    "\n",
    "```Top level keys in characters in scenes are:\n",
    "\t sex\n",
    "\t name\n",
    "\t weapon\n",
    "\t born\n",
    "\t married\n",
    "\t killedBy\n",
    "\t mannerOfDeath\n",
    "\t title\n",
    "\t alive\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Your data processing and cleanse tasks is to create a table of the form:\n",
    "    - ```seasonNum```\n",
    "    - ```episodeNum```\n",
    "    - ```sceneNo```\n",
    "    - ```character_id_1```\n",
    "    - ```action```\n",
    "    - ```character_id_2```\n",
    "    \n",
    "    \n",
    "- You can get access to information that maps ```characterName``` to ```character_id``` using the following code."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pymysql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "# cloud_conn = pymysql.connect(\n",
    "#     host=\"columbia8013.cqgsme1nmjms.us-east-1.rds.amazonaws.com\",\n",
    "#     user=\"gotuser\",\n",
    "#     password=\"gotusergotuser\",\n",
    "#     db=\"W4111GoTSolutionClean\",\n",
    "#     cursorclass=pymysql.cursors.DictCursor)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = pymysql.connect(          #connect to my local database\n",
    "    host=\"localhost\",\n",
    "    user=\"root\",\n",
    "    password=\"ttl980305\",\n",
    "    db=\"W4111GoTSolutionClean\",\n",
    "    cursorclass=pymysql.cursors.DictCursor)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_character_info(c_name):\n",
    "    \n",
    "    sql = \"select * from character_ids where characterName=%s\"\n",
    "    cur = conn.cursor()\n",
    "    res = cur.execute(sql, (c_name))\n",
    "    res = cur.fetchall()\n",
    "    try:\n",
    "        return res[0]['character_id']\n",
    "    except:\n",
    "        return None\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "get_character_info(\"Boar\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Answer"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-I extract information from json file and then export dataframe into csv file."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<u>Python Code</u>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd  # read json and produce csv\n",
    "\n",
    "df=pd.read_json('.\\Data\\json\\episodes.json')       #len(df)=73\n",
    "data={'seasonNum':[],'episodeNum':[],'sceneNo':[],'character_id_1':[],'action':[],'character_id_2':[]}\n",
    "\n",
    "for i in range(len(df['episodes'])):\n",
    "    for j in range(len(df['episodes'][i]['scenes'])):\n",
    "        for x in df['episodes'][i]['scenes'][j]['characters']:\n",
    "            #sex\n",
    "            if x.get('sex')!=None:\n",
    "                if len(x['sex']['with'])==1:\n",
    "                    data['seasonNum'].append(df['episodes'][i]['seasonNum'])\n",
    "                    data['episodeNum'].append(df['episodes'][i]['episodeNum'])\n",
    "                    data['sceneNo'].append(j)\n",
    "                    data['character_id_1'].append(get_character_info(x['name']))\n",
    "                    data['action'].append('sex')\n",
    "                    data['character_id_2'].append(get_character_info(x['sex']['with'][0]))\n",
    "                else:\n",
    "                    for k in x['sex']['with']:        #sex with multiple people\n",
    "                        data['seasonNum'].append(df['episodes'][i]['seasonNum'])\n",
    "                        data['episodeNum'].append(df['episodes'][i]['episodeNum'])\n",
    "                        data['sceneNo'].append(j)\n",
    "                        data['character_id_1'].append(get_character_info(x['name']))\n",
    "                        data['action'].append('sex')\n",
    "                        data['character_id_2'].append(get_character_info(k))\n",
    "            #married      \n",
    "            if x.get('married')!=None:\n",
    "                data['seasonNum'].append(df['episodes'][i]['seasonNum'])\n",
    "                data['episodeNum'].append(df['episodes'][i]['episodeNum'])\n",
    "                data['sceneNo'].append(j)\n",
    "                data['character_id_1'].append(get_character_info(x['name']))\n",
    "                data['action'].append('married')\n",
    "                data['character_id_2'].append(get_character_info(x['married']['to']))\n",
    "            #killedBy\n",
    "            if x.get('killedBy')!=None:\n",
    "                data['seasonNum'].append(df['episodes'][i]['seasonNum'])\n",
    "                data['episodeNum'].append(df['episodes'][i]['episodeNum'])\n",
    "                data['sceneNo'].append(j)\n",
    "                data['character_id_1'].append(get_character_info(x['name']))\n",
    "                data['action'].append('killedBy')\n",
    "                if x['killedBy']!=[]:\n",
    "                    data['character_id_2'].append(get_character_info(x['killedBy'][0]))\n",
    "                else:\n",
    "                    data['character_id_2'].append(None)\n",
    "                \n",
    "df=pd.DataFrame(data)\n",
    "df.to_csv('.\\Data\\csv\\events.csv',index=False)\n",
    "aa=pd.read_csv('.\\Data\\csv\\events.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<u>Table Definition</u>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<u>Sample Query</u>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@localhost/W4111GoTSolutionclean\n",
      "20 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>seasonNum</th>\n",
       "        <th>episodeNum</th>\n",
       "        <th>sceneNo</th>\n",
       "        <th>character_id_1</th>\n",
       "        <th>action</th>\n",
       "        <th>character_id_2</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>9</td>\n",
       "        <td>CH_360</td>\n",
       "        <td>killedBy</td>\n",
       "        <td>CH_362</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>12</td>\n",
       "        <td>CH_68</td>\n",
       "        <td>killedBy</td>\n",
       "        <td>CH_362</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>14</td>\n",
       "        <td>CH_368</td>\n",
       "        <td>killedBy</td>\n",
       "        <td>CH_59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>16</td>\n",
       "        <td>CH_122</td>\n",
       "        <td>killedBy</td>\n",
       "        <td>CH_184</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>22</td>\n",
       "        <td>CH_349</td>\n",
       "        <td>sex</td>\n",
       "        <td>CH_293</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>22</td>\n",
       "        <td>CH_293</td>\n",
       "        <td>sex</td>\n",
       "        <td>CH_349</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>32</td>\n",
       "        <td>CH_133</td>\n",
       "        <td>married</td>\n",
       "        <td>CH_44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>32</td>\n",
       "        <td>CH_44</td>\n",
       "        <td>married</td>\n",
       "        <td>CH_133</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>33</td>\n",
       "        <td>CH_133</td>\n",
       "        <td>sex</td>\n",
       "        <td>CH_44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>33</td>\n",
       "        <td>CH_44</td>\n",
       "        <td>sex</td>\n",
       "        <td>CH_133</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>35</td>\n",
       "        <td>CH_113</td>\n",
       "        <td>sex</td>\n",
       "        <td>CH_38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>35</td>\n",
       "        <td>CH_38</td>\n",
       "        <td>sex</td>\n",
       "        <td>CH_113</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>2</td>\n",
       "        <td>10</td>\n",
       "        <td>CH_133</td>\n",
       "        <td>sex</td>\n",
       "        <td>CH_44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>2</td>\n",
       "        <td>10</td>\n",
       "        <td>CH_44</td>\n",
       "        <td>sex</td>\n",
       "        <td>CH_133</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>2</td>\n",
       "        <td>12</td>\n",
       "        <td>CH_37</td>\n",
       "        <td>killedBy</td>\n",
       "        <td>CH_326</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>2</td>\n",
       "        <td>19</td>\n",
       "        <td>CH_133</td>\n",
       "        <td>sex</td>\n",
       "        <td>CH_44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>2</td>\n",
       "        <td>19</td>\n",
       "        <td>CH_44</td>\n",
       "        <td>sex</td>\n",
       "        <td>CH_133</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>2</td>\n",
       "        <td>25</td>\n",
       "        <td>CH_227</td>\n",
       "        <td>killedBy</td>\n",
       "        <td>CH_299</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>2</td>\n",
       "        <td>29</td>\n",
       "        <td>CH_150</td>\n",
       "        <td>killedBy</td>\n",
       "        <td>CH_59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>3</td>\n",
       "        <td>22</td>\n",
       "        <td>CH_44</td>\n",
       "        <td>sex</td>\n",
       "        <td>CH_133</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 1, 9, 'CH_360', 'killedBy', 'CH_362'),\n",
       " (1, 1, 12, 'CH_68', 'killedBy', 'CH_362'),\n",
       " (1, 1, 14, 'CH_368', 'killedBy', 'CH_59'),\n",
       " (1, 1, 16, 'CH_122', 'killedBy', 'CH_184'),\n",
       " (1, 1, 22, 'CH_349', 'sex', 'CH_293'),\n",
       " (1, 1, 22, 'CH_293', 'sex', 'CH_349'),\n",
       " (1, 1, 32, 'CH_133', 'married', 'CH_44'),\n",
       " (1, 1, 32, 'CH_44', 'married', 'CH_133'),\n",
       " (1, 1, 33, 'CH_133', 'sex', 'CH_44'),\n",
       " (1, 1, 33, 'CH_44', 'sex', 'CH_133'),\n",
       " (1, 1, 35, 'CH_113', 'sex', 'CH_38'),\n",
       " (1, 1, 35, 'CH_38', 'sex', 'CH_113'),\n",
       " (1, 2, 10, 'CH_133', 'sex', 'CH_44'),\n",
       " (1, 2, 10, 'CH_44', 'sex', 'CH_133'),\n",
       " (1, 2, 12, 'CH_37', 'killedBy', 'CH_326'),\n",
       " (1, 2, 19, 'CH_133', 'sex', 'CH_44'),\n",
       " (1, 2, 19, 'CH_44', 'sex', 'CH_133'),\n",
       " (1, 2, 25, 'CH_227', 'killedBy', 'CH_299'),\n",
       " (1, 2, 29, 'CH_150', 'killedBy', 'CH_59'),\n",
       " (1, 3, 22, 'CH_44', 'sex', 'CH_133')]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select * from events limit 20;           # my event table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Data Access Abstraction\n",
    "\n",
    "- A common API and abstraction for manipulating different database types and schema simplifies the development of web applications and other applications. There are many abstractions.\n",
    "\n",
    "| <img src=\"./Lecture_4_ER_SQL_Relational_GoT_9.jpg\"> |\n",
    "| :---: |\n",
    "| __Data Access Objects__ |\n",
    "\n",
    "- The second programming tasks is to implement our own abstraction. This is RDBDataTable, which implements an abstraction we will use in subsequent homework assignments, and which we will use to enable our web application.\n",
    "\n",
    "\n",
    "- You can find the implementation template in the ```src``` directory of the project template.\n",
    "\n",
    "\n",
    "- For HW 1, Part C, you can copy your implementation code into this notebook and test again the cloud databases.\n",
    "\n",
    "\n",
    "- __I strongly suggest that you develop and test in PyCharm, and then copy your code into the notebook. The cells below just show you where to place your code. You should copy your code over from PyCharm.__"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Implementation\n",
    "\n",
    "- ```BaseDataTable``` defines the common abstraction we will use. You do not need to do anything to this class."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import package to enable defining abstract classes in Python.\n",
    "# Do not worry about understanding abstract base classes. This is just a class that defines\n",
    "# some methods that subclasses must implement.\n",
    "from abc import ABC, abstractmethod\n",
    "\n",
    "\n",
    "class DataTableException(Exception):\n",
    "    \"\"\"\n",
    "    A simple class that maps underlying implementation exceptions to generic exceptions.\n",
    "    \"\"\"\n",
    "\n",
    "    invalid_method = 1001\n",
    "\n",
    "    # General\n",
    "    def __init__(self, code, message):\n",
    "        self.code = code\n",
    "        self.message = message\n",
    "\n",
    "    def __str__(self):\n",
    "        result = (\n",
    "            type(self), {\"code\": self.code, \"message\": self.message}\n",
    "        )\n",
    "        result = str(result)\n",
    "        return result\n",
    "\n",
    "\n",
    "class BaseDataTable(ABC):\n",
    "    \"\"\"\n",
    "    The implementation classes (XXXDataTable) for CSV database, relational, etc. will extend the\n",
    "    base class and implement the abstract methods.\n",
    "    \"\"\"\n",
    "\n",
    "    def __init__(self, entity_type_name, connect_info, key_columns=None, context=None):\n",
    "        \"\"\"\n",
    "\n",
    "        :param entity_type_name: Name of the logcal entity type. This maps to various abstractions in\n",
    "            underlying stores, e.g. file names, RDB tables, Neo4j Labels, ...\n",
    "        :param connect_info: Dictionary of parameters necessary to connect to the data. See examples in subclasses.\n",
    "        :param key_columns: List, in order, of the columns (fields) that comprise the primary key.\n",
    "            A primary key is a set of columns whose values are unique and uniquely identify a row. For Appearances,\n",
    "            the columns are ['playerID', 'teamID', 'yearID']\n",
    "        :param contex: Holds context and environment information.\n",
    "        \"\"\"\n",
    "        self._table_name = entity_type_name\n",
    "        self._connect_info = connect_info\n",
    "        self._key_columns = key_columns\n",
    "        self._context = context\n",
    "\n",
    "    @abstractmethod\n",
    "    def find_by_primary_key(self, key_fields, field_list=None, context=None):\n",
    "        \"\"\"\n",
    "\n",
    "        :param key_fields: The values for the key_columns, in order, to use to find a record. For example,\n",
    "            for Appearances this could be ['willite01', 'BOS', '1960']\n",
    "        :param field_list: A subset of the fields of the record to return. The CSV file or RDB table may have many\n",
    "            additional columns, but the caller only requests this subset.\n",
    "        :return: None, or a dictionary containing the columns/values for the row.\n",
    "        \"\"\"\n",
    "        pass\n",
    "\n",
    "    @abstractmethod\n",
    "    def find_by_template(self, template, field_list=None, limit=None, offset=None, order_by=None, context=None):\n",
    "        \"\"\"\n",
    "\n",
    "        :param template: A dictionary of the form { \"field1\" : value1, \"field2\": value2, ...}. The function will return\n",
    "            a derived table containing the rows that match the template.\n",
    "        :param field_list: A list of requested fields of the form, ['fielda', 'fieldb', ...]\n",
    "        :param limit: Do not worry about this for now.\n",
    "        :param offset: Do not worry about this for now.\n",
    "        :param order_by: Do not worry about this for now.\n",
    "        :return: A derived table containing the computed rows.\n",
    "        \"\"\"\n",
    "        pass\n",
    "\n",
    "    @abstractmethod\n",
    "    def insert(self, new_entity, context=None):\n",
    "        \"\"\"\n",
    "\n",
    "        :param new_record: A dictionary representing a row to add to the set of records. Raises an exception if this\n",
    "            creates a duplicate primary key.\n",
    "        :return: None\n",
    "        \"\"\"\n",
    "        pass\n",
    "\n",
    "    @abstractmethod\n",
    "    def delete_by_template(self, template, context=None):\n",
    "        \"\"\"\n",
    "\n",
    "        Deletes all records that match the template.\n",
    "\n",
    "        :param template: A template.\n",
    "        :return: A count of the rows deleted.\n",
    "        \"\"\"\n",
    "        pass\n",
    "\n",
    "    @abstractmethod\n",
    "    def delete_by_key(self, key_fields, Context=None):\n",
    "        \"\"\"\n",
    "\n",
    "        Delete record with corresponding key.\n",
    "\n",
    "        :param key_fields: List containing the values for the key columns\n",
    "        :return: A count of the rows deleted.\n",
    "        \"\"\"\n",
    "        pass\n",
    "\n",
    "    @abstractmethod\n",
    "    def update_by_template(self, template, new_values, context=None):\n",
    "        \"\"\"\n",
    "\n",
    "        :param template: A template that defines which matching rows to update.\n",
    "        :param new_values: A dictionary containing fields and the values to set for the corresponding fields\n",
    "            in the records. This returns an error if the update would create a duplicate primary key. NO ROWS are\n",
    "            update on this error.\n",
    "        :return: The number of rows updates.\n",
    "        \"\"\"\n",
    "        pass\n",
    "\n",
    "    @abstractmethod\n",
    "    def update_by_key(self, key_fields, new_values, context=None):\n",
    "        \"\"\"\n",
    "\n",
    "        :param key_fields: List of values for primary key fields\n",
    "        :param new_values: A dictionary containing fields and the values to set for the corresponding fields\n",
    "            in the records. This returns an error if the update would create a duplicate primary key. NO ROWS are\n",
    "            update on this error.\n",
    "        :return: The number of rows updates.\n",
    "        \"\"\"\n",
    "        pass\n",
    "\n",
    "    @abstractmethod\n",
    "    def query(self, query_statement, args, context=None):\n",
    "        \"\"\"\n",
    "        Passed through/executes a raw query in the native implementation language of the backend.\n",
    "        :param query_statement: Query statement as a string.\n",
    "        :param args: Args to insert into query if it is a template\n",
    "        :param context:\n",
    "        :return: A JSON object containing the result of the operation.\n",
    "        \"\"\"\n",
    "        pass\n",
    "\n",
    "    @abstractmethod\n",
    "    def load(self, rows=None):\n",
    "        \"\"\"\n",
    "        Loads data into the data table.\n",
    "        :param rows:\n",
    "        :return: Number of rows loaded.\n",
    "        \"\"\"\n",
    "\n",
    "    @abstractmethod\n",
    "    def save(self, context):\n",
    "        \"\"\"\n",
    "        Writes any cached data to a backing store.\n",
    "        :param context:\n",
    "        :return:\n",
    "        \"\"\"\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "__RDBDataTable__\n",
    "\n",
    "\n",
    "- You only need to implement the following methods:\n",
    "    - ```__init__```\n",
    "    - ```__str__```\n",
    "    - ```find_by_template``` You only need to implement the parameters\n",
    "        - ```template```\n",
    "        - ```field_list```\n",
    "    - ```find_by_primary_key``` You only need to implement the parameters\n",
    "        - ```key_fields```\n",
    "        - ```field_list```\n",
    "\n",
    "\n",
    "- The logical behavior of the ```find_by_template``` API is the following:\n",
    "    - If you create an ```RDBDataTable``` instance for a SQL table ```GoTSolutionClean.scenes_characters,```\n",
    "    - The call<br>\n",
    "```\n",
    "    characters_tbl.find_by_template(\n",
    "            template={\n",
    "                'seasonNum': 1,\n",
    "                'episodeNum': 2\n",
    "                },\n",
    "            field_list = ['seasonNum', 'episodeNum', 'sceneNo', 'character_id']\n",
    "            )\n",
    "```\n",
    "    - Is equivalent to\n",
    "```\n",
    "    select seasonNum, episodeNum, sceneNo, character_id\n",
    "        from GoTSolutionClean.scenes_characters\n",
    "        where\n",
    "            seasonNum=1 and episodeNum=1\n",
    "```\n",
    "    - And returns the same dictionary.\n",
    "    \n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- The logical behavior of the ```find_by_primary_key``` API is the following:\n",
    "    - If you create an ```RDBDataTable``` instance for a SQL table ```GoTSolutionClean.episodes,```\n",
    "    - The primary key columns are ```seasonNum, episodeNum.```\n",
    "    - The invocation would be ```find_by_primary_key([1,3],field_list=['seasonNum','episodeNum','title'])```\n",
    "    - This would get converted into\n",
    "```\n",
    "select seasonNum, episodeNum, title\n",
    "        from W4111GoTSolutionClean.episodes\n",
    "        where\n",
    "            seasonNum=1 and episodeNum=1\n",
    "```\n",
    "    - Your class implementation MUST run a database query to determine the primary key columns and their order to form the ```where``` clause.\n",
    "    - The result is a single dictionary containing the query result.\n",
    "    \n",
    "    \n",
    "- We will provide tests."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "from src.data_tables.BaseDataTable import BaseDataTable\n",
    "from src.data_tables.BaseDataTable import  DataTableException\n",
    "\n",
    "import pandas as pd\n",
    "import pymysql\n",
    "\n",
    "import logging\n",
    "\n",
    "logger = logging.getLogger()\n",
    "\n",
    "\n",
    "class RDBDataTable(BaseDataTable):\n",
    "    \"\"\"\n",
    "    RDBDataTable is relation DB implementation of the BaseDataTable.\n",
    "    \"\"\"\n",
    "\n",
    "    # Default connection information in case the code does not pass an object\n",
    "    # specific connection on object creation.\n",
    "    _default_connect_info = pymysql.connect(\n",
    "        host=\"columbia8013.cqgsme1nmjms.us-east-1.rds.amazonaws.com\",\n",
    "        user=\"gotuser\",\n",
    "        password=\"gotusergotuser\",\n",
    "        db=\"W4111GoTSolutionClean\",\n",
    "        cursorclass=pymysql.cursors.DictCursor)\n",
    "\n",
    "    _default_cnx = None\n",
    "\n",
    "    @classmethod\n",
    "    def _get_default_cnx(cls):\n",
    "\n",
    "        if RDBDataTable._default_cnx is None:\n",
    "            # Create a connection to use inside this object. In general, this is not the right approach.\n",
    "            # There would be a connection pool shared across many classes and applications.\n",
    "            cls._default_cnx = pymysql.connect(\n",
    "                host=\"columbia8013.cqgsme1nmjms.us-east-1.rds.amazonaws.com\",\n",
    "                user=\"gotuser\",\n",
    "                password=\"gotusergotuser\",\n",
    "                db=\"W4111GoTSolutionClean\",\n",
    "                cursorclass=pymysql.cursors.DictCursor)\n",
    "\n",
    "            return cls._default_cnx\n",
    "\n",
    "        return RDBDataTable._default_cnx\n",
    "\n",
    "    def __init__(self, table_name, key_columns=None, connect_info=None):\n",
    "        \"\"\"\n",
    "\n",
    "        :param table_name: The name of the RDB table.\n",
    "        :param connect_info: Dictionary of parameters necessary to connect to the data.\n",
    "        :param key_columns: List, in order, of the columns (fields) that comprise the primary key.\n",
    "            This is for compatibility with other types of data table. Any value other than None is an error.\n",
    "        \"\"\"\n",
    "\n",
    "        # If there is not explicit connect information, use the defaults.\n",
    "\n",
    "        self._cnx = None\n",
    "        self._connect_info = None\n",
    "        self._key_columns = None\n",
    "        self._columns = None\n",
    "\n",
    "        if key_columns is not None:\n",
    "            raise ValueError(\"This is an RBD Data Table. We figure out the keys by querying the DB.\")\n",
    "\n",
    "        self._table_name = table_name\n",
    "        self._key_columns = self.get_key_columns()\n",
    "        self._columns = self.get_columns()\n",
    "\n",
    "    def get_row_count(self):\n",
    "        sql = \"select count(*) as count from \" + self._table_name\n",
    "        res = self._run_q(sql, fetch=True)\n",
    "        return res[0]['count']\n",
    "\n",
    "    def get_key_columns(self):\n",
    "        sql = \"show keys from \" + self._table_name + \" where Key_name = 'PRIMARY'\"\n",
    "        res = self._run_q(sql, fetch=True)\n",
    "        return [i['Column_name'] for i in res]\n",
    "\n",
    "    def __str__(self):\n",
    "        \"\"\"\n",
    "\n",
    "        :return: String representation of the table's metadata.\n",
    "        \"\"\"\n",
    "        result = \"RDBDataTable \" + self._table_name\n",
    "        result += \"\\n\\tRows = \" + str(self.get_row_count())\n",
    "        result += \"\\n\\tKey Columns = \" + str(self._key_columns)\n",
    "        result += \"\\n\\tColumns = \" + str(self._columns)\n",
    "        return result\n",
    "\n",
    "    def get_columns(self):\n",
    "\n",
    "        if self._columns is None:\n",
    "            sql = \"describe \" + self._table_name\n",
    "            res = self._run_q(sql, fetch=True)\n",
    "\n",
    "            result = []\n",
    "\n",
    "            for r in res:\n",
    "                result.append({'column_name': r['Field'],\n",
    "                               'type': r['Type'],\n",
    "                               'nullable': r['Null']})\n",
    "            return result\n",
    "\n",
    "    @classmethod\n",
    "    def _run_q(cls, q, args=None, fields=None, fetch=True, cnx=None, cursor=None, commit=True):\n",
    "        \"\"\"\n",
    "\n",
    "        :param q: An SQL query string that may have %s slots for argument insertion. The string\n",
    "            may also have {} after select for columns to choose.\n",
    "        :param args: A tuple of values to insert in the %s slots.\n",
    "        :param fetch: If true, return the result.\n",
    "        :param cnx: A database connection. May be None\n",
    "        :param cncursor: Do not worry about this for now.\n",
    "        :param commit: Do not worry about this for now. This is more wizard stuff.\n",
    "        :return: A result set or None.\n",
    "        \"\"\"\n",
    "\n",
    "        r = None\n",
    "\n",
    "        cursor_created = False\n",
    "\n",
    "        if cnx is None:\n",
    "            cnx = cls._get_default_cnx()\n",
    "\n",
    "        try:\n",
    "            # Use the connection in the object if no connection provided.\n",
    "\n",
    "            # Convert the list of columns into the form \"col1, col2, ...\" for following SELECT.\n",
    "            if fields:\n",
    "                q = q.format(\",\".join(fields))\n",
    "\n",
    "            if cursor is None:\n",
    "                cursor = cnx.cursor()  # Just ignore this for now.\n",
    "                cursor_created = True\n",
    "\n",
    "            # If debugging is turned on, will print the query sent to the database.\n",
    "            # self.debug_message(\"Query = \", cursor.mogrify(q, args))\n",
    "\n",
    "            cursor.execute(q, args)  # Execute the query.\n",
    "\n",
    "            # Technically, INSERT, UPDATE and DELETE do not return results.\n",
    "            # Sometimes the connector libraries return the number of created/deleted rows.\n",
    "            if fetch:\n",
    "                r = cursor.fetchall()  # Return all elements of the result.\n",
    "            else:\n",
    "                r = None\n",
    "\n",
    "            if commit:  # Do not worry about this for now.\n",
    "                cnx.commit()\n",
    "\n",
    "            if cursor_created:\n",
    "                cursor.close()\n",
    "\n",
    "        except Exception as e:\n",
    "            print(\"Exception e = \", e)\n",
    "            if commit:\n",
    "                cnx.rollback()\n",
    "            if cursor_created:\n",
    "                cursor.close()\n",
    "\n",
    "        return r\n",
    "\n",
    "    def _run_insert(self, table_name, column_list, values_list, cnx=None, commit=True):\n",
    "        \"\"\"\n",
    "\n",
    "        :param table_name: Name of the table to insert data. Probably should just get from the object data.\n",
    "        :param column_list: List of columns for insert.\n",
    "        :param values_list: List of column values.\n",
    "        :param cnx: Ignore this for now.\n",
    "        :param commit: Ignore this for now.\n",
    "        :return:\n",
    "        \"\"\"\n",
    "        try:\n",
    "            q = \"insert into \" + table_name + \" \"\n",
    "\n",
    "            # If the column list is not None, form the (col1, col2, ...) part of the statement.\n",
    "            if column_list is not None:\n",
    "                q += \"(\" + \",\".join(column_list) + \") \"\n",
    "\n",
    "            # We will use query parameters. For a term of the form values(%s, %s, ...) with one slot for\n",
    "            # each value to insert.\n",
    "            values = [\"%s\"] * len(values_list)\n",
    "\n",
    "            # Form the values(%s, %s, ...) part of the statement.\n",
    "            values = \" ( \" + \",\".join(values) + \") \"\n",
    "            values = \"values\" + values\n",
    "\n",
    "            # Put all together.\n",
    "            q += values\n",
    "\n",
    "            self._run_q(q, args=values_list, fields=None, fetch=False, cnx=cnx, commit=commit)\n",
    "\n",
    "        except Exception as e:\n",
    "            print(\"Got exception = \", e)\n",
    "            raise e\n",
    "\n",
    "    @classmethod\n",
    "    def get_folders(cls):\n",
    "        pass\n",
    "\n",
    "    def find_by_primary_key(self, key_fields, field_list=None, context=None):\n",
    "        \"\"\"\n",
    "\n",
    "        :param key_fields: The values for the key_columns, in order, to use to find a record.\n",
    "        :param field_list: A subset of the fields of the record to return.\n",
    "        :return: None, or a dictionary containing the request fields for the record identified\n",
    "            by the key.\n",
    "        \"\"\"\n",
    "\n",
    "        if len(self._key_columns) == 0:\n",
    "            e = DataTableException(DataTableException.invalid_method,\n",
    "                                   \"Table does not have any keys\")\n",
    "            raise e\n",
    "\n",
    "        template = dict(zip(self._key_columns, key_fields))\n",
    "        result = self.find_by_template(template, field_list)\n",
    "        if result is not None and len(result)>0:\n",
    "            result = result[0]\n",
    "        else:\n",
    "            result = None\n",
    "        return result\n",
    "\n",
    "    def _template_to_where_clause(self, t):\n",
    "        \"\"\"\n",
    "        Convert a query template into a WHERE clause.\n",
    "        :param t: Query template.\n",
    "        :return: (WHERE clause, arg values for %s in clause)\n",
    "        \"\"\"\n",
    "        w_clause = \"\"\n",
    "        args = []\n",
    "        terms = []\n",
    "\n",
    "        for k, v in t.items():\n",
    "            temp_s = k + \"=%s \"\n",
    "            terms.append(temp_s)\n",
    "            args.append(v)\n",
    "\n",
    "        if len(terms) > 0:\n",
    "            w_clause = \"where \" + \" and \".join(terms)\n",
    "        else:\n",
    "            w_clause = \"\"\n",
    "            args = None\n",
    "\n",
    "        return w_clause, args\n",
    "\n",
    "    def find_by_template(self, template, field_list=None, limit=None, offset=None, order_by=None, commit=True):\n",
    "        \"\"\"\n",
    "\n",
    "        :param template: A dictionary of the form { \"field1\" : value1, \"field2\": value2, ...}\n",
    "        :param field_list: A list of request fields of the form, ['fielda', 'fieldb', ...]\n",
    "        :param limit: Do not worry about this for now.\n",
    "        :param offset: Do not worry about this for now.\n",
    "        :param order_by: Do not worry about this for now.\n",
    "        :return: A list containing dictionaries. A dictionary is in the list representing each record\n",
    "            that matches the template. The dictionary only contains the requested fields.\n",
    "        \"\"\"\n",
    "        q = \"select {} from \" + self._table_name\n",
    "\n",
    "        w_clause, args = self._template_to_where_clause(template)\n",
    "        q += \" \" + w_clause\n",
    "\n",
    "        result = self._run_q(q, args, field_list, fetch=True)\n",
    "\n",
    "        return result\n",
    "\n",
    "    def insert(self, new_record):\n",
    "        \"\"\"\n",
    "\n",
    "        :param new_record: A dictionary representing a row to add to the set of records.\n",
    "        :return: None\n",
    "        \"\"\"\n",
    "        pass\n",
    "\n",
    "    def delete_by_template(self, template):\n",
    "        \"\"\"\n",
    "\n",
    "        Deletes all records that match the template.\n",
    "\n",
    "        :param template: A template.\n",
    "        :return: A count of the rows deleted.\n",
    "        \"\"\"\n",
    "        pass\n",
    "\n",
    "    def delete_by_key(self, key_fields):\n",
    "        \"\"\"\n",
    "\n",
    "        Delete record with corresponding key.\n",
    "\n",
    "        :param key_fields: List containing the values for the key columns\n",
    "        :return: A count of the rows deleted.\n",
    "        \"\"\"\n",
    "        pass\n",
    "\n",
    "    def update_by_template(self, template, new_values):\n",
    "        \"\"\"\n",
    "\n",
    "        :param template: A template that defines which matching rows to update.\n",
    "        :param new_values: A dictionary containing fields and the values to set for the corresponding fields\n",
    "            in the records. This returns an error if the update would create a duplicate primary key. NO ROWS are\n",
    "            update on this error.\n",
    "        :return: The number of rows updates.\n",
    "        \"\"\"\n",
    "        pass\n",
    "\n",
    "    def update_by_key(self, key_fields, new_values):\n",
    "        \"\"\"\n",
    "\n",
    "        :param key_fields: List of values for primary key fields\n",
    "        :param new_values: A dictionary containing fields and the values to set for the corresponding fields\n",
    "            in the records. This returns an error if the update would create a duplicate primary key. NO ROWS are\n",
    "            update on this error.\n",
    "        :return: The number of rows updates.\n",
    "        \"\"\"\n",
    "        pass\n",
    "\n",
    "    def load(self):\n",
    "        pass\n",
    "\n",
    "    def save(self):\n",
    "        pass\n",
    "\n",
    "    def query(self, query_statement, args, context=None):\n",
    "        pass\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "__Tests__\n",
    "\n",
    "My test: \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "from src.data_tables.RDBDataTable import RDBDataTable\n",
    "import json\n",
    "\n",
    "def t1():\n",
    "\n",
    "    print(\"\\n************** t1 ****************\\n\")\n",
    "\n",
    "    tbl = RDBDataTable('W4111GoTSolutionClean.episodes')\n",
    "    print(\"Table = \", tbl)             #test __inti__ and __str__\n",
    "    print(\"\\n\")\n",
    "\n",
    "    print(\"\\n************** t1 ****************\\n\")\n",
    "\n",
    "def t2():\n",
    "\n",
    "    print(\"\\n************** t2 ****************\\n\")\n",
    "\n",
    "    tbl = RDBDataTable('W4111GoTSolutionClean.episodes')\n",
    "\n",
    "    res = tbl.find_by_template(          #test find_by_template\n",
    "        {\"seasonNum\": 1, 'episodeNum': 2},\n",
    "        field_list={'seasonNum', 'episodeNum', 'episodeTitle'}\n",
    "    )\n",
    "    print(\"Answer =\")\n",
    "    print(json.dumps(res, indent=2, default=str))\n",
    "\n",
    "    print(\"\\n************** t2 ****************\\n\")\n",
    "\n",
    "def t3():                                #test find_by_primary_key\n",
    "\n",
    "    print(\"\\n************** t3 ****************\\n\")\n",
    "\n",
    "    tbl = RDBDataTable('W4111GoTSolutionClean.scenes')\n",
    "\n",
    "    res = tbl.find_by_primary_key([3, 1, 11],\n",
    "        field_list=['seasonNum', 'episodeNum', 'sceneNo', 'location', 'subLocation']\n",
    "    )\n",
    "\n",
    "    print(\"Answer =\\n\")\n",
    "    print(json.dumps(res, indent=2, default=str))\n",
    "\n",
    "    print(\"\\n************** t3 ****************\\n\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "************** t1 ****************\n",
      "\n",
      "Table =  RDBDataTable W4111GoTSolutionClean.episodes\n",
      "\tRows = 73\n",
      "\tKey Columns = ['id']\n",
      "\tColumns = [{'column_name': 'id', 'type': 'int(11)', 'nullable': 'NO'}, {'column_name': 'seasonNum', 'type': 'int(11)', 'nullable': 'NO'}, {'column_name': 'episodeNum', 'type': 'int(11)', 'nullable': 'NO'}, {'column_name': 'episodeTitle', 'type': 'varchar(64)', 'nullable': 'NO'}, {'column_name': 'episodeLink', 'type': 'varchar(64)', 'nullable': 'NO'}, {'column_name': 'episodeAirDate', 'type': 'date', 'nullable': 'NO'}, {'column_name': 'episodeDescription', 'type': 'varchar(256)', 'nullable': 'NO'}]\n",
      "\n",
      "\n",
      "\n",
      "************** t1 ****************\n",
      "\n"
     ]
    }
   ],
   "source": [
    "t1()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "************** t2 ****************\n",
      "\n",
      "Answer =\n",
      "[\n",
      "  {\n",
      "    \"episodeTitle\": \"The Kingsroad\",\n",
      "    \"episodeNum\": 2,\n",
      "    \"seasonNum\": 1\n",
      "  }\n",
      "]\n",
      "\n",
      "************** t2 ****************\n",
      "\n"
     ]
    }
   ],
   "source": [
    "t2()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "************** t3 ****************\n",
      "\n",
      "Answer =\n",
      "\n",
      "{\n",
      "  \"seasonNum\": 3,\n",
      "  \"episodeNum\": 1,\n",
      "  \"sceneNo\": 11,\n",
      "  \"location\": \"North of the Wall\",\n",
      "  \"subLocation\": \"Frostfang Mountains\"\n",
      "}\n",
      "\n",
      "************** t3 ****************\n",
      "\n"
     ]
    }
   ],
   "source": [
    "t3()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  },
  "toc": {
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
